[SQL] niet-ingevulde koppeltabelvelden alsnog meenemen in 1q

[BalusC]

Ik heb een paar (fictieve) tabellen.

1) 'vendor' dat een bouwer aangeeft.
2) 'vendor_category' dat bouwer-specifieke categorieën aangeeft (gekoppeld via FK), met een nummeriek veld 'order' dat de bouwer-specifieke categorie volgorde aangeeft.
3) 'product_category' dat alle mogelijke waarden van een bouwer-specifieke categorie aangeeft (gekoppeld via FK).
4) 'user_category' een koppeltabel met 'user' als ene FK en 'product_category' als andere FK.
5) 'user' dat een eindgebruiker aangeeft.

Nu kan het voorkomen dat de 'user_category' niet alle mogelijke 'vendor_category' waarden dekt en zou ik in een query daarop graag in de juiste volgorde NULL terugkrijgen. Bijvoorbeeld: er zijn 5 bouwer-specifieke categorieen in vendor_category en daarvan zijn slechts de eerste, derde en vijfde ingevuld in user_category en dan zou ik graag in 1 query de waarden willen hebben in vorm van waarde1, NULL, waarde3, NULL, waarde5.

Echter kan ik dit na diverse pogingen niet bewerkstelligen. Al mijn pogingen leiden tot slechts waarde1, waarde3, waarde5 of juist ALLE mogelijke waarden, of juist ALLE mogelijke waarden als NULL behalve waarde1, waarde3 en waarde5.

Deze query bijvoorbeeld retourneert waarde1, waarde3, waarde5.

SQL:

SELECT 
        pc.value
FROM 
        product_category pc

INNER JOIN
        user_category uc
    ON
        uc.pc_id = pc.id
    AND
        uc.id = 1

LEFT JOIN
        vendor_category vc
    ON
        vc.id = pc.vc_id

ORDER BY
        vc.order

Hoe krijg ik hier waarde1, NULL, waarde3, NULL, waarde5 uit?

In de werkelijkheid wordt deze query als subquery gebruikt dat een ARRAY retourneert, maar dit is verder niet van belang lijkt me.

[remco_k]

Ik heb je post nog niet helemaal goed gelezen, maar zou LEFT JOIN niet LEFT OUTER JOIN moeten zijn?

[ Voor 4% gewijzigd door remco_k op 10-06-2009 16:58 ]

[jvaneijk]

remco_k schreef op woensdag 10 juni 2009 @ 16:57:
Ik heb je post nog niet helemaal goed gelezen, maar zou LEFT JOIN niet LEFT OUTER JOIN moeten zijn?

Volgens mij is dat helemaal taal afhankelijk.

[Standeman]

Denk het niet, staat heel mooi in de SQL standaard omschreven.

[P_de_B]

Gebruik je velden van vc in de WHERE?

[SKiLLa]

The JOIN-volgorde al eens andersom geprobeerd ?

[BalusC]

remco_k: helaas, geen verschil. Gaat om PostgreSQL 8.3 overigens.
P_de_B: nope, die worden alleen gebruikt in de JOIN. Exact de geposte SQL query is gebruikt.
SKiLLa: helaas, geen verschil, ook niet met LEFT OUTER.

[ Voor 14% gewijzigd door BalusC op 10-06-2009 17:23 ]

[Crazybest]

in where clausule vergelijk je altijd waarden die daar aan voldoen en filtert NULL dus ook weg. Als je daarmee zit moet je de ISNULL validatie gebruiken.. beetje googlen doet wonderen.

[RobertMe]

RIGHT JOIN? Je wilt toch alle rijen uit vendor_categorie wel hebben? En vender_categorie is hier de rechter tabel, dus zou het RIGHT JOIN moeten zijn.

[BalusC]

RobertMe schreef op woensdag 10 juni 2009 @ 17:22:
RIGHT JOIN? Je wilt toch alle rijen uit vendor_categorie wel hebben? En vender_categorie is hier de rechter tabel, dus zou het RIGHT JOIN moeten zijn.

RIGHT JOIN ipv INNER JOIN levert dus een NULL van elk unmatchede product_category op en ook nog niet in de volgorde. Ik wil dus een NULL van elk unmatchede vendor_category hebben in de juiste volgorde.

Eigenlijk zou ik de vendor_category RIGHT JOIN'en, maar dit werkt op een of andere manier niet. Ik krijg slechts de ingevulde waarden terug.

[ Voor 16% gewijzigd door BalusC op 10-06-2009 17:29 ]

[BalusC]

SQL:

SELECT 
        pc.value
FROM 
        product_category pc

INNER JOIN -- of RIGHT JOIN or RIGHT OUTER JOIN, allemaal zelfde
        user_category uc
    ON
        uc.pc_id = pc.id
    AND
        uc.id = 1

RIGHT JOIN
        vendor_category vc
    ON
        vc.id = pc.vc_id

ORDER BY
        vc.order

Deze komt dus ook in de buurt. Maar dit filtert op product_category ipv vendor_category. Ik krijg dus een NULL voor ELK unmatchede product_category van dezelfde vendor.

[ Voor 18% gewijzigd door BalusC op 10-06-2009 17:53 ]

[RobertMe]

Maar dit filtert op product_category ipv vendor_category

Hoe bedoel je? Als het goed is haalt die elke vc op, en als het mogelijk is, ook nog de pc die erbij hoort.

Heb je anders wat voorbeeld data, en hoe jij wil dat die data eruit lolt, want ik snap ook niet precies wat je nu wil.

[BalusC]

Even fictief:

vendor: audi, bmw, mercedes, etc..
vendor_category: audi(engine), audi(quattro), bmw(engine), bmw(idrive), etc..
product_category: audi(engine(2.5)), audi(engine(3.0)), audi(quattro(manual)), audi(quattro(automatic)), etc..
user_category: user1-audi(engine(3.0)), user2-audi(engine(2.5)), user2-audi(quattro(automatic)), etc..
user: user1, user2, user3, etc..

De user heeft van vendor_category voor audi enkel de product_category voor audi(engine) ingevuld en niet audi(quattro). Ik wil voor user1 dus 3.0, NULL hieruit hebben (hetgeen de gebruiker heeft gekozen per vendor_category) en dus NIET 3.0, NULL, NULL, NULL, etc.. (dus een NULL voor elk niet-gekozen product_category, wat nergens op slaat).

[BalusC]

Ik heb even voorbeelddata gemaakt (mysql-vriendelijk en keys buitengelaten):

SQL:

CREATE TABLE vendor (id integer(8), name varchar(20));
INSERT INTO vendor (id, name) VALUES (1, 'audi'), (2, 'bmw'), (3, 'merc');
CREATE TABLE vendor_category (id integer(8), v_id integer(8), name varchar(20), ordering integer(2));
INSERT INTO vendor_category (id, v_id, name, ordering) VALUES (1, 1, 'engine', 1), (2, 1, 'quattro', 2), (3, 2, 'engine', 1), (4, 2, 'idrive', 2);
CREATE TABLE product_category (id integer(8), vc_id integer(8), value varchar(20));
INSERT INTO product_category (id, vc_id, value) VALUES (1, 1, '2.5'), (2, 1, '3.0'), (3, 2, 'manual'), (4, 2, 'automatic');
CREATE TABLE user (id integer(8), name varchar(20));
INSERT INTO user (id, name) VALUES (1, 'user1'), (2, 'user2'), (3, 'user3');
CREATE TABLE user_category (u_id integer(8), pc_id integer(8));
INSERT INTO user_category (u_id, pc_id) VALUES (1, 2);

Hier is de licht bijgewerkte query (uc.id had uc.u_id moeten zijn en 'order' is hernoemd naar 'ordering'):

SQL:

SELECT 
        pc.value
FROM 
        product_category pc

INNER JOIN -- of RIGHT JOIN or RIGHT OUTER JOIN, allemaal zelfde
        user_category uc
    ON
        uc.pc_id = pc.id
    AND
        uc.u_id = 1

RIGHT JOIN
        vendor_category vc
    ON
        vc.id = pc.vc_id

ORDER BY
        vc.ordering

Dit retourneert 3.0, NULL, NULL, NULL terwijl ik 3.0, NULL moet hebben.
Wanneer ik RIGHT JOIN van vc vervang door LEFT JOIN, dan krijg ik enkel de non-null waarde 3.0.

[RobertMe]

Dit retourneert 3.0, NULL, NULL, NULL terwijl ik 3.0, NULL moet hebben.

Waarom moet je 3.0, NULL hebben? Zonder redenering daarachter is het ook niet mogelijk

Met een DISTINCT heb je dat resultaat trouwens, moet je alleen de ORDER BY weg halen (die sowieso al redelijk nutteloos is, omdat je die kolom zelf helemaal niet op haalt)

offtopic:
Vind het ook wel mooi dat je die CREATE TABLE op pgsql kreeg gedraaid, ik kreeg toch meteen error op integer(8 ), aangezien een integer geen lengte/grote kan hebben

edit:
En dan lees je dat MySQL vriendelijk erboven staat

[ Voor 5% gewijzigd door RobertMe op 10-06-2009 19:30 ]

[BalusC]

RobertMe schreef op woensdag 10 juni 2009 @ 19:28:
[...]

Waarom moet je 3.0, NULL hebben? Zonder redenering daarachter is het ook niet mogelijk

Ik wil dus voor ALLE vendor_category de gekozen waarden in de juiste volgorde hebben (ze worden uiteindelijk getoond in preselected dropdowns met alle opties uit product_category).

Met een DISTINCT heb je dat resultaat trouwens, moet je alleen de ORDER BY weg halen (die sowieso al redelijk nutteloos is, omdat je die kolom zelf helemaal niet op haalt)

Nee, dat vat ALLE NULL's samen tot 1 NULL. Stel dat er 5 categorieen zijn waarvan 2e en 4e niet zijn ingevuld, zoals in TS genoemd.

offtopic:
Vind het ook wel mooi dat je die CREATE TABLE op pgsql kreeg gedraaid, ik kreeg toch meteen error op integer(8 ), aangezien een integer geen lengte/grote kan hebben

Ik 'speel' liever met mysql dan postgres.

[ Voor 31% gewijzigd door BalusC op 10-06-2009 19:47 ]

[Voutloos]

De query uit de startpost, met enkel LEFT JOINS en dan iets als IF(uc.id IS NULL, NULL, pc.value) selecteren?

[cariolive23]

1) vendor_category bevat 4 records, logisch dat je dan met een RIGHT JOIN 4 resultaten krijgt, waaronder een paar NULL's.
2) Wanneer je alleen pc.value opvraagt, zal nooit duidelijk worden waarom je een NULL krijgt, je ziet geen enkel verband met andere waardes en records. Vraag dus alle gegevens op uit de tabellen waar je mee aan het werk bent. Later kun je overbodige data wel weer verwijderen, maar met debuggen heb je dit even nodig om een goed beeld te krijgen.

MySQL is niet relevant, zonder fatsoenlijke configuratie, die zomaar weer kan veranderen, is dit niet betrouwbaar. Heb je relatief weinig aan.

[BalusC]

cariolive23 schreef op woensdag 10 juni 2009 @ 19:51:
1) vendor_category bevat 4 records, logisch dat je dan met een RIGHT JOIN 4 resultaten krijgt, waaronder een paar NULL's.

D'oh.

Voor als het je nog niet is opgevallen, de hoeveelheid nulls is identiek aan hoeveelheid niet-gekozen product_category waarden van dezelfde vendor!

2) Wanneer je alleen pc.value opvraagt, zal nooit duidelijk worden waarom je een NULL krijgt, je ziet geen enkel verband met andere waardes en records. Vraag dus alle gegevens op uit de tabellen waar je mee aan het werk bent. Later kun je overbodige data wel weer verwijderen, maar met debuggen heb je dit even nodig om een goed beeld te krijgen.

Ik zoek gewoon een efficiente manier om in 1 query alle gekozen waarden voor de vendor_category in de juiste volgorde te hebben. Ik kan natuurlijk achteraf nog 1 query erop afvuren en programmeertechnisch de records verwerken. Maar da's verre van efficient.

MySQL is niet relevant, zonder fatsoenlijke configuratie, die zomaar weer kan veranderen, is dit niet betrouwbaar. Heb je relatief weinig aan.

Eh? Ik ga gewoon uit van ANSI SQL. Zoals vermeld is de uiteindelijke DB postgres en speel ik met mysql.

Dank voor je nuttige bijdrage in dit topic. Mocht je hard bewijs hebben dat dit gewoonweg niet mogelijk is, dan hoor ik het ook graag aan.

[ Voor 7% gewijzigd door BalusC op 10-06-2009 20:28 ]

[Voutloos]

Het moet wel efficient kunnen en ik hoop dat ik dicht bij de oplossing zit.

Cariolive23 rant altijd op mysql, en hoewel regelmatig terecht, zitten we nu niet in de buurt van berucht mysql gedrag.

SQL:

SELECT 
       pc.value,
       uc.u_id,
       IF(uc.u_id IS NULL, NULL, pc.value) bauke_blij
FROM 
        product_category pc
LEFT JOIN         user_category uc
    ON
    (uc.pc_id = pc.id AND uc.u_id = 1)
LEFT JOIN
        vendor_category vc
    ON
        vc.id = pc.vc_id
ORDER BY
        vc.ordering

Doet in ieder geval wat ik qua wens uit de startpost haal. Als je een specifieke vendor bedoelt is dat een triviale wijziging. Als je het ongeacht user wil, kan het ook wel, met gebruik van IN() of GROUP BY.

[ Voor 61% gewijzigd door Voutloos op 10-06-2009 20:25 ]

[RobertMe]

Cariolive23 rant altijd op mysql, en hoewel regelmatig terecht, zitten we nu niet in de buurt van berucht mysql gedrag.

Intussen wel...

function if(boolean, unknown, character varying) does not exist

Cariolive, kom er maar in

En als ik hem omschrijf naar CASE WHEN, krijg ik 4 records terug, en dat lijkt niet te zijn wat de TS wil

[BalusC]

Inderdaad .. Er moet dus een theoretische GROUP BY komen op vendor_category.

[BalusC]

En met een vieze MAX() en GROUP BY 'hack' heb ik het nu voor elkaar op zowel pg als mysql:

SQL:

SELECT 
        max(pc.value)
    FROM 
        product_category pc

INNER JOIN
        user_category uc
    ON
        uc.pc_id = pc.id
    AND
        uc.u_id = 1

RIGHT JOIN
        vendor_category vc
    ON
        vc.id = pc.vc_id

GROUP BY
        vc.ordering

ORDER BY
        vc.ordering

Mijn doel is bereikt.

Dank voor het meedenken

[ Voor 5% gewijzigd door BalusC op 10-06-2009 21:40 ]

[Voutloos]

RobertMe schreef op woensdag 10 juni 2009 @ 20:33:
[...]
Intussen wel...

Cariolive, kom er maar in

Klopt, maar CASE WHEN werkt idd ook en anders zoek je maar je eigen control flow functions op. Iig niets spannend om een standaard mysql-is-onbetrouwbaar-rant ertegen aan te gooien.

En als ik hem omschrijf naar CASE WHEN, krijg ik 4 records terug, en dat lijkt niet te zijn wat de TS wil

Vandaar mijn laatste opmerkingen, er zijn in de test set zowel 4 uc's als pc's, dus ik vraag me af waarom er dan 2 resultaten moeten terugkomen.

[RobertMe]

BalusC schreef op woensdag 10 juni 2009 @ 20:41:
En met een vieze MAX() en GROUP BY 'hack' heb ik het nu voor elkaar op zowel pg als mysql:

Uhm, kom je dan niet in de problemen als er meerdere "ordering" zijn met hetzelfde nummer? Dan vallen er dus weg (aangezien ik je situatie nog steeds niet snap kan ik niet aangeven of dat wel of niet de bedoeling is)

Voutloos schreef op woensdag 10 juni 2009 @ 20:42:
[...]
Klopt, maar CASE WHEN werkt idd ook en anders zoek je maar je eigen control flow functions op. Iig niets spannend om een standaard mysql-is-onbetrouwbaar-rant ertegen aan te gooien.

TS gaf aan dat het om ANSI SQL ging, dan valt IF dus af (MySQL only) en komt CASE om te hoek kijken (wel ANSI als ik me niet vergis, en als ie niet ANSI is werkt ie op een stuk meer DB's dan IF)

[BalusC]

RobertMe schreef op woensdag 10 juni 2009 @ 20:48:
Uhm, kom je dan niet in de problemen als er meerdere "ordering" zijn met hetzelfde nummer? Dan vallen er dus weg (aangezien ik je situatie nog steeds niet snap kan ik niet aangeven of dat wel of niet de bedoeling is)

Dit is uitgesloten. In de werkelijkheid is het paar 'ordering' en 'v_id' op UNIQUE gezet.