[php+mysql] join statement werkt niet zoals verwacht

[rob3rt]

Ik wil uit een tabel een waarde selecteren welke niet voorkomt in een andere tabel
Deze querie gebruik ik hiervoor:

PHP:

 SELECT a.server_id, a.server_anummer
FROM server_hardware AS a
LEFT JOIN server_lijst AS b ON a.server_anummer = b.server_anummer
WHERE a.managed = '19'
AND b.server_anummer IS NULL

Wat ik wil is alle server_anummers welke in server_hardware voorkomen maar niet in server_lijst en een managed waarde van '19' hebben.
Als ik bovenstaande querie geeft op de database komt er 0 als result terug, hij zou echter 2 resultaten moeten vinden in de database die voldoen hieraan.

Ik heb me er inmiddels op blind gestaard en diverse variaties toegepast maar ik kan maar niet de juiste querie vinden.

Iemand een tip/idee wat ik hier verkeerd doe?

[NMe]

SQL:

SELECT server_id, server_nummer
FROM server_hardware
WHERE managed = 19 AND server_id NOT IN
(
  SELECT sh.server_id
  FROM server_hardware sh INNER JOIN server_lijst sl ON sh.server_anummer = sl.server_anummer
)

Jouw query gaat trouwens fout omdat je eerst alle records selecteert die wél in allebei voorkomen met die left join, om vervolgens weer álle gevonden records af te keren met je laatste "WHERE b.server_anummer IS NULL". NULL is nooit gelijk aan NULL, want NULL is immers niet gedefinieerd en kan dus nooit met zekerheid gelijk zijn aan een andere ongedefinieerde waarde, die records waar server_anummer NULL is krijg je al niet terug; vervolgens geef je in je WHERE wél aan dat je alleen die records wil zien waar server_anummer NULL is. Dat is tegenstrijdig en dus krijg je terecht niks terug.

[ Voor 56% gewijzigd door NMe op 07-01-2010 03:37 ]

[Voutloos]

NMe schreef op donderdag 07 januari 2010 @ 03:29:
Jouw query gaat trouwens fout omdat je eerst alle records selecteert die wél in allebei voorkomen met die left join

Nope, het is een left join, geen inner join.

om vervolgens weer álle gevonden records af te keren met je laatste "WHERE b.server_anummer IS NULL".

Nope, als niet ge-left-joined kan worden krijg je voor de rechterterm allemaal null values.

NULL is nooit gelijk aan NULL, want NULL is immers niet gedefinieerd en kan dus nooit met zekerheid gelijk zijn aan een andere ongedefinieerde waarde, die records waar server_anummer NULL is krijg je al niet terug;

Mooi stukje theorie, maar daarom is het ook IS NULL.

Jouw query zal in mysql altijd hetzelfde resultaat opleveren als de query uit de ts, tenzij ik nu nog een andere fout over het hoofd zie.

[Verwijderd]

Het gebruik van een outer join kan bij sommige databases (Oracle) en bepaalde omstandigheden (grootte van tabellen, indexen, etc.) voordeliger zijn dan het gebruik van NOT IN of NOT EXISTS. rob3rt, je query werkt niet omdat je nu de koppelkolom van server_lijst test op NULL. Deze is echter altijd gelijk aan a.server_anummer, en niet NULL. Waarschijnlijk werkt je query wel als je een andere kolom van server_lijst test op NULL:

PHP:

 SELECT a.server_id, a.server_anummer
FROM server_hardware AS a
LEFT JOIN server_lijst AS b ON a.server_anummer = b.server_anummer
WHERE a.managed = '19'
AND b.<kolom, anders dan b.server_anummer, die normaal niet null kan zijn> IS NULL

[NMe]

Voutloos schreef op donderdag 07 januari 2010 @ 09:08:
[...]
Nope, het is een left join, geen inner join.
[...]
Nope, als niet ge-left-joined kan worden krijg je voor de rechterterm allemaal null values.

Hmm, het was laat.

Mooi stukje theorie, maar daarom is het ook IS NULL.

Ik had het dan ook niet over die vergelijking an sich maar over de vergelijking die binnen de join zelf gebeurt.

Jouw query zal in mysql altijd hetzelfde resultaat opleveren als de query uit de ts, tenzij ik nu nog een andere fout over het hoofd zie.

Mja, ze lijken bij nader inzien inderdaad behoorlijk synoniem. Maar goed, voor zover ik bij elkaar kan puzzelen met de eisen uit de topicstart en de informatie die daarin gegeven wordt kan ik in mijn query in elk geval geen denkfout ontdekken, dus als die niet werkt zit er iets niet goed in de uitleg in de topicstart ofwel er zit iets niet goed in de data zelf.

[Verwijderd]

Voutloos schreef op donderdag 07 januari 2010 @ 09:08:
Nope, als niet ge-left-joined kan worden krijg je voor de rechterterm allemaal null values.

Geen idee hoe het in mySQL zit, maar in Oracle krijg je bij een ontbrekend join record voor de rechterterm allemaal null values, behalve de kolom waarop je joint; die krijgt de waarde van de overeenkomende kolom aan de linkerkant.

[Psychokiller]

Verwijderd schreef op donderdag 07 januari 2010 @ 13:28:
[...]Geen idee hoe het in mySQL zit, maar in Oracle krijg je bij een ontbrekend join record voor de rechterterm allemaal null values, behalve de kolom waarop je joint; die krijgt de waarde van de overeenkomende kolom aan de linkerkant.

Bij MySQL krijgt de niet gevonden rij állemaal NULL values, ook de kolom waarop je joined. VInd het een beetje vreemd dat Oracle die kolom dan wel zou kunnen invullen, terwijl die dus geen match vind.

Voor dit geval geldt in ieder geval dat als de LEFT JOIN niet gemaakt kan worden, er uit b.server_anummer ook de waarde NULL komt.

De query opzich lijkt dan ook goed en je zult het eerder moeten zoeken in de data.
Heb je server_hardware.managed niet toevallig als char i.p.v varchar (of eigenlijk int) opgeslagen waardoor je met spaties zit en de WHERE mislukt ?

Wat krijg je als je die WHERE weglaat en de velden eens selecteerd om te kijken.

[ Voor 14% gewijzigd door Psychokiller op 07-01-2010 14:29 ]

[Verwijderd]

Het ziet er naar uit dat ik terug naar school moet; ook in Oracle worden alle kolommen op NULL gezet als er geen match wordt gevonden. Mea culpa.

[cariolive23]

Verwijderd schreef op vrijdag 08 januari 2010 @ 09:09:
Het ziet er naar uit dat ik terug naar school moet; ook in Oracle worden alle kolommen op NULL gezet als er geen match wordt gevonden. Mea culpa.

Uiteraard, anders zou Oracle helemaal geen LEFT of RIGHT joins (evt. met een alternatieve syntax) ondersteunen. Dit is namelijk een eigenschap van het resultaat dit soort joins. Wat er niet is, kun je ook niet invullen en dus krijg je een NULL.

[Voutloos]

Nou ja, dan wordt het wachten op de topicstarter, die de gewenste query mogelijk verkeerd uitlegt, of simpelweg niet die data in de DB heeft.