[MSSQL] OUTER JOIN raadsel

[Kama]

Ik zit met een vreemd verschijnsel. Ik zal wel iets over het hoofd zien, maar een query doet in MS SQL niet wat ik verwacht:

Probleem: Ik heb een table met filenames uit projecten van verschillende versies:

projectid	versionid	filename
1	1	myfile.txt
1	1	deleted.txt
1	2	myfile.txt

Zoals verwacht vormen deze drie kolommen de PK en zijn projectid en versionid FK's. Er zijn ook nog wat andere kolommen, maar die doen er even niet toe.

Ik wil nu weten welke files in versie 2 zijn verwijderd, tov versie 1. Dus alle versie 1's, waarvoor geen versie 2 kan worden gevonden. Dus een left outer join, waarbij de rechter table NULL is... Dus:

SQL:

select a.* from FILES a
left outer join FILES b
on
 a.projectid = b.projectid 
 and a.filename = b.filename
where
 a.versionid =  1
 and b.versionid = 2
 and a.projectid = 1
 and b.versionid is NULL

Ik verwacht hier dus alle files die wel in versie 1 zitten en niet in versie 2. Tot mijn verassing krijg ik echter niks terug (0 records). Als ik de "b.versionid is NULL" weghaal uit de where clause, zie ik dat ik gewoon de inner join terug krijg... Wat zie ik over het hoofd?

edit:
Overigens ook een full outer join geeft een inner join terug.

[BHR]

Moet de

SQL:

and b.versionid = 2 

niet tevens in de on-clause?

[Kama]

BHR schreef op woensdag 11 oktober 2006 @ 17:59:Moet de

SQL:

and b.versionid = 2 

niet tevens in de on-clause?

Ik kan zelf niet beredeneren waarom (ik zou kunnen begrijpen dat het de b.version.. in de where vervangt). Maar omdat ik dit probleem heb omdat ik waarschijnlijk niet niet zelf goed beredeneer heb ik het wel uitgeprobeerd, maar het werkt ook niet ...

[EdwinG]

SQL:

b.versionid = 2
..
 and b.versionid is NULL

Dus b.versionid moet zowel 2 ALS NULL zijn?

[Verwijderd]

(b.versionid = 2) AND (b.versionid is null) ? In mijn boekje is dat altijd False !!

[ Voor 112% gewijzigd door Verwijderd op 11-10-2006 18:13 ]

[Verwijderd]

EdwinG schreef op woensdag 11 oktober 2006 @ 18:07:

SQL:

b.versionid = 2
..
 and b.versionid is NULL

Dus b.versionid moet zowel 2 ALS NULL zijn?

Inderdaad, deze query zal nooit en te nimmer een record opleveren.

Probeer het eens met subqueries, that will do the trick

[whoami]

Hmm, je hebt wel een beetje van een brak data-model denk ik, maar ik ga me er niet echt over uitspreken, want ik weet niet echt wat de bedoeling is..

Ivm de opmerking van BHR, heb je die 'and b.versionid = 2' wel weggelaten uit de WHERE clause ?

Als je die conditie in de WHERE zet, dan zal er eerst gejoined worden, en daarna worden die versionid's 2 eruit gefiltert.
Als je die conditie in de JOIN clause zet, dan wordt er eerst de join toegepast, en daarna pas gefiltert. Nu overlappen regel 8 en regel 10 elkaar in je query. (b.versionid kan niet gelijk NULL en 2 zijn).

[Kama]

Verwijderd schreef op woensdag 11 oktober 2006 @ 18:09:
(b.versionid = 2) AND (b.versionid is null) ? In mijn boekje is dat altijd False !!

Jullie hebben natuurlijk gelijk... Ik heb in m'n eigenlijke code b.filename gebruikt... Maar het zet me wel aan het denken, want in feite wil ik wel dat b.versionid==null, dus als ik nou es die b.versionid wel in de ON zet ipv de WHERE, dan werkt het misschien...

edit:
Yep, dit lost het op... de code is nu

SQL:

select a.* from FILES a 
left outer join FILES b 
on 
 a.projectid = b.projectid  
 and a.filename = b.filename 
 and b.versionid = 2
where 
 a.versionid =  1 
 and a.projectid = 1 
 and b.versionid is NULL

Beetje zonde van m'n middag... Dank allen...

[ Voor 27% gewijzigd door Kama op 11-10-2006 18:17 ]

[EdwinG]

Werkt dit niet gewoon makkelijker?

SQL:

SELECT filename
   FROM FILES
   WHERE versionid = 1 AND
      projectid = 1 AND
      file NOT IN 
         (SELECT filename
         FROM FILES
         WHERE project = 1 AND
            version = 2)

edit:
FFrenzy: 2x te langzaam

(al dacht ik dat jouw vorige post ook deze oplossing had, zal wel scheel worden )

[ Voor 28% gewijzigd door EdwinG op 11-10-2006 18:16 ]

[Verwijderd]

misschien dat dit wel werkt :

SQL:

Select 
  * 
from 
  filenames 
where 
  projectid=1 and 
  versionid=1 and 
  filename not in ( select filename from filenames where projectid=1 and versionid=2 )

[edit]..damn, te laat

[ Voor 7% gewijzigd door Verwijderd op 11-10-2006 18:14 ]

[Kama]

whoami schreef op woensdag 11 oktober 2006 @ 18:12:
Hmm, je hebt wel een beetje van een brak data-model denk ik, maar ik ga me er niet echt over uitspreken, want ik weet niet echt wat de bedoeling is..

Volgens mij valt dat reuze mee: Ik heb nog een tabel PROJECT en een tabel VERSIE. De eerste bevat de statische projectgegevens en de tweede de versiespecifieke data (Versienr, datum, etc.). De bedoeling: Verschillende versies van het project opslaan in de database.

Als je nog tips hebt, hoor ik het graag.

[Kama]

EdwinG schreef op woensdag 11 oktober 2006 @ 18:13:
Werkt dit niet gewoon makkelijker?

SQL:

SELECT filename
   FROM FILES
   WHERE versionid = 1 AND
      projectid = 1 AND
      file NOT IN 
         (SELECT filename
         FROM FILES
         WHERE project = 1 AND
            version = 2)

De subquery wordt potentieel voor ieder record uit de select opnieuw uitgevoerd (en met die NOT is dat dan dus een kwadratische query). Nu zal de database dat wel optimaliseren, waardoor het netto niet veel meer uitmaakt, maar ik krijg er nog altijd een slecht gevoel bij.

[Verwijderd]

EdwinG schreef op woensdag 11 oktober 2006 @ 18:13:

edit:
FFrenzy: 2x te langzaam

(al dacht ik dat jouw vorige post ook deze oplossing had, zal wel scheel worden )

offtopic:
klopt, ik had mijn eerste post ge-edit, en de oplossing toegevoegd. Maar omdat dat niet duidelijk was, heb ik hem weer aangepast en in een aparte post gezet. Alleen waren er toen opeens meerdere mensen me al voor

[PhysicsRules]

Je zou nog kunnen doen

SQL:

select a.* from FILES a 
left outer join (select c.* from FILES c where c.versionid = 2 and c.projectid = 1) b
on 
 a.projectid = b.projectid  
 and a.filename = b.filename 
where 
 a.versionid =  1 
 and a.projectid = 1 
 and b.versionid is NULL