MySQL, selecteer waar 4 kolommen gedeeltelijk overeenkomen

[Jarige]

Ik heb twee tabellen die met elkaar vergeleken moeten worden, en op basis van een overeenkomst moet van een van de tabellen een Id worden genomen.

De eerste tabel is een tabel met een Id en 4 kolommen, die allemaal NOT NULL zijn. De tweede tabel lijkt sterk op de eerste; een Id kolom en 4 kolommen, maar deze keer mogen ze NULL zijn, en dat gebeurt ook.
Nou wil ik uit tabel2 de Id's hebben die een overeenkomst hebben één of meerdere waarden in tabel1.

Stel tabel1 bevat deze waarden in de 4 kolommen; auto,fiets,bus,trein en in tabel2 staat een row met alleen auto in de eerste kolom dan moet die alsnog geselecteerd worden, omdat de eerste kolom overeenkomt.

Als tabel2 een row bevat die bijv. auto,lopend,bus bevat in de eerste 3 kolommen, dan wordt die niet geselecteerd, aangezien lopend niet hetzelfde is als fiets.

Als er een lege waarde is in tabel2, dan mag daar alles voor in de plaats komen.
Het is een beetje moeilijk uit te leggen, maar ik hoop dat jullie het begrijpen.
Mijn vraag is, hoe doe ik dit?
Ik heb geprobeert met JOINS te werken, maar dat ging niet. Ik heb geprobeert met een hele hoop IN's te werken, maar dat werkte niet. Ik heb gezocht en geprobeert, al uren lang, maar ik kom er niet uit.

Kan iemand me een query geven die dit doet, of een duw in de goede richting, een tip of hint?

Wordt erg op prijs gesteld

[pedorus]

Janoz legt het beter uit, zie onder.

[ Voor 83% gewijzigd door pedorus op 12-01-2010 19:22 ]

[Janoz]

Volgens mij heb je maar 1 join nodig. Waar je over na moet denken is hoe je de geschreven tekst omzet in een expressie. Waar het namelijk eigenlijk op neer komt is dat de twee velden gelijk moeten zijn of het tweede veld null. Dat kun je uitdrukken met twee vergelijkingen en een OR. Vervolgens moet dat voor alle 4 de kolommen (met AND) en je bent klaar.

[T020]

zoiets misschien?

SQL:

SELECT *
FROM tabel1
WHERE
(tabel1.kolom1 = tabel2.kolom1 OR tabel2.kolom1 IS NULL) AND
(tabel1.kolom2 = tabel2.kolom2 OR tabel2.kolom2 IS NULL) AND
(tabel1.kolom3 = tabel2.kolom3 OR tabel2.kolom3 IS NULL) AND
(tabel1.kolom4 = tabel2.kolom4 OR tabel2.kolom4 IS NULL);

ik weet niet of de expressie "iets = NULL" is toegestaan in SQL maar iets soortgelijks moet te doen zijn lijkt me

edit: bedankt MueR

[ Voor 4% gewijzigd door T020 op 12-01-2010 19:39 ]

[MueR]

SQL:

WHERE KolomNaam IS NULL

dan

[pedorus]

Overigens, als oefening in driewaardige logica:
[code=sql,4]
(tabel1.kolom1 = tabel2.kolom1 AND
tabel1.kolom2 = tabel2.kolom2 AND
tabel1.kolom3 = tabel2.kolom3 AND
tabel1.kolom4 = tabel2.kolom4 AND NULL) IS NULL;
[/code=sql]
Ik weet enkel niet hoe leuk de SQL-optimizer dit gaat vinden..

[Jarige]

Bedankt voor jullie reacties. Ik heb nu ineens een ander probleem dat hier wel mee te maken heeft;
Ik voer eerst een query uit die een tijdelijke tabel maakt (dat wordt tabel1 in dit verhaal). Tabel2 is zoals het hoort al aanwezig.
Ik doe dus
CREATE TEMPORARY TABLE table1 (kolom1 (type), kolom2 .... enz) SELECT * FROM .. WHERE ... ;
SELECT *
FROM tabel1
WHERE
(tabel1.kolom1 = tabel2.kolom1 OR tabel2.kolom1 IS NULL) AND
(tabel1.kolom2 = tabel2.kolom2 OR tabel2.kolom2 IS NULL) AND
(tabel1.kolom3 = tabel2.kolom3 OR tabel2.kolom3 IS NULL) AND
(tabel1.kolom4 = tabel2.kolom4 OR tabel2.kolom4 IS NULL);

De eerste query gaat zonder syntaxfouten, de tweede ook, maar de tweede gaat verkeerd; hij herkent table2.kolom1 niet (en waarschijnlijk dus de hele tabel niet). Ik dacht dat een tijdelijke tabel alleen voor die connection was, maar de verbinding is niet verbroken tussendoor. Hij stuurt alles rechtstreeks met mysql_query($query) en dat doet hij dus in 1 verbinding. Wat kan hier aan de hand zijn?

[Verwijderd]

Waar is je join dan? Je selecteert nu alleen uit tabel1, dus is het logisch dat hij geen kolommen genaamd tabel2.nogwat kan vinden.

Overigens heb ik sterk het vermoeden dat je niet goed hebt genormaliseerd.

[Jarige]

#1054 - Unknown column 'table2.kolom1' in 'where clause'

Dat is de exacte fout, terwijl ik table2 daarboven heb aangemaakt. En op zich heb ik alleen data uit de eerste kolom nodig, dus geen join...

Ik heb geprobeert zoveel mogelijk te normaliseren, samen met een vriend van me, en dit leek ons de meest genormaliseerde oplossing van alle oplossingen die we hadden (je moest eens weten wat ons oorspronkelijke plan was! Dat was echt een klassiek voorbeeld van gedenormaliseerd!).

[pedorus]

Het helpt misschien als je een sql tutorial doorneemt, want dan had je geweten dat je de from niet letterlijk moest kopiëren van T020.

Bovendien doe je een join. Je kan in principe die hele where-clause ook in een on-clause opnemen.

[Verwijderd]

SQL:

SELECT *
FROM tabel1
WHERE ...

Waar zie jij tabel2 staan dan? Nergens!

Dat je alleen spul uit tabel1 nodig hebt is niet waar, want je where-clause heeft wel tabel2 nodig, dus moet je gewoon een join hebben.

[Jarige]

Ik dacht dat T020 een query gaf die ik gewoon gebruiken kon. Om eerlijk te zijn ging ik blind op hem af, heb er niet echt naar gekeken. Ik heb wel meerdere tutorials gelezen, maar dit is mijn eerste wat grotere project (voor school btw).
Ok, joins dus. Ik heb er van gehoord, en ik heb ze geprobeert te gebruiken om dit probleem op te lossen, maar nog niet op de manier waarop jullie het willen vermoed ik. Ik heb nu joins gebruikt, en ik krijg alweer een foutmelding. Deze keer eentje die ik nog nooit gezien heb:
#1267 - Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

Zoals altijd, bedankt voor de reacties. Ik probeer deze foutmelding uit te zoeken. Ik neem aan dat het te maken heeft met de manier waarop de tijdelijke tabel wordt gemaakt, maar waarom is hij swedish!?

[Jarige]

Ik heb nu de foutmelding weten op te lossen. Ik moest bij het maken van de tijdelijke tabel COLLATE latin1_general_ci zetten achter elke kolom. Het schijnt door een bug te komen dat hij automatisch een zweedse collation neemt.
In ieder geval, de query geeft geen foutmelding meer, maar hij geeft een empty result set, wat ook niet helemaal de bedoeling is. Ik ben aan het onderzoeken, en ik kom hier als ik er niet uit kom...

[T020]

Verwijderd schreef op dinsdag 12 januari 2010 @ 21:59:
Waar zie jij tabel2 staan dan? Nergens!

Sorry, mijn fout idd

dan zou het moeten worden:

SQL:

SELECT tabel1.id
FROM tabel1
INNER JOIN tabel2 ON
(tabel1.kolom1 = tabel2.kolom1 OR tabel2.kolom1 IS NULL) AND 
(tabel1.kolom2 = tabel2.kolom2 OR tabel2.kolom2 IS NULL) AND 
(tabel1.kolom3 = tabel2.kolom3 OR tabel2.kolom3 IS NULL) AND 
(tabel1.kolom4 = tabel2.kolom4 OR tabel2.kolom4 IS NULL);

Dit is overigens maar een voorbeeld, "tabel1", "tabel2", "kolom1" enz. moet je natuurlijk vervangen door je eigen tabel- en veldnamen

[Jarige]

Dat is precies de code die ik ook heb, maar dan idd eigen tabel en veldnamen. Het probleem is, dan die niet werkt. Hij geeft geen enkele waarde terug terwijl er wel degelijk overeenkomsten tussen de eerste en tweede tabel zijn... Ik ben nu letterlijk elke subquery aan het uitvoeren om te kijken of de output goed is...

[Compuhair]

volgens mij moet de query ook wat uitgebreider worden.

Zoiets in pseudo code:

select *
where
(
tabel1.col1 == tabel2.col1 and tabel2.col1 is not null
and
tabel1.col2 == tabel2.col2 and tabel2.col2 is not null
and
tabel1.col3 == tabel2.col3 and tabel2.col3 is not null
and
tabel1.col4 == tabel2.col4 and tabel2.col4 is not null
)
OR
(
tabel1.col1 == tabel2.col1 and tabel2.col1 is not null
and
tabel1.col2 == tabel2.col2 and tabel2.col2 is not null
and
tabel1.col3 == tabel2.col3 and tabel2.col3 is not null
and
tabel2.col4 is null
)
OR
(
tabel1.col1 == tabel2.col1 and tabel2.col1 is not null
and
tabel1.col2 == tabel2.col2 and tabel2.col2 is not null
and
tabel2.col3 is null
and
tabel2.col4 is null
)
OR
(
tabel1.col1 == tabel2.col1 and tabel2.col1 is not null
and
tabel2.col2 is null
and
tabel2.col3 is null
and
tabel2.col4 is null
)

[Janoz]

Dat is een zeer uitgebreide expressie die in de eerste plaats enkele dingen nogal dubbelop doet ¹ en een stuk restrictiever is². Aangezien de TS met de eerder gegeven query al geen resultaten terug krijgt kan het niet zijn dat deze query beter is.


1) Bij een expressie als A == B and B is not NULL is het tweede deel overbodig. Als B gelijk is aan NULL dan kan A == B nooit true worden. Als het eerste deel waar is zal het tweede deel altijd waar zijn en kan dus net zo goed weggelaten worden.

2) De eerder gegeven query vindt ook records die in kolom 1 en 3 waardes heeft terwijl kolom 2 en 4 NULL zijn. Je hebt dus nog niet de complete permutatie genomen. De vraag blijft of dat ook nodig is. Uit de TS is niet goed op te maken of alle NULL velden aan het einde moeten zitten of dat er ook een NULL in het midden mag zitten.

[ Voor 6% gewijzigd door Janoz op 13-01-2010 12:00 ]

[Comp_Lex]

Ik zie trouwens nog geen goede reden waarom je niet beter 1 tabel kan maken waar iets NULL mag zijn.

[Jarige]

Ik heb het opgelost. Het probleem zat hem in IS NULL, aangezien de lege waarden niet NULL waren, maar of 0 of een lege string. Ik heb ze dus nu geselecteerd op basis van =0 en =''
Bedankt voor de hulp

[pedorus]

Zou het niet beter zijn geweest om de dataset te verbeteren? 0 of '' waar je NULL bedoelt lijkt me niet zo handig.

[T020]

inderdaad, je heb wss de optie NOT NULL aangevinkt bij het aanmaken van de tabel, dan geeft ie een default waarde aan elk veld die past bij het datatype. Als je de NOT NULL optie voor elk van de 4 kolommen weer uitzet werkt het wel gewoon, dat is wel zo netjes