[SQL] "Orphan" records zoeken

[Verwijderd]

Ik heb in ACCESS twee tabellen van 75000 records en ongeveer 20 velden.
In beide tablellen is er een veld dat een bijna uniek nummer bevat.
In tabel1 is dat veld2 en in tabel2 is dat veld 5.

1 record in tabel1 kan refereren naar meerdere records in tabel 2 als je kijkt
of veld2 en veld5 gelijk zijn.

Nu is het helaas zo gekomen dat er in tabel1 records staan die geen overeenkomstige records meer hebben in tabel2. Ook is het helaas zo dat tabel2 records heeft zonder overeenkomstige records in tabel1. Een zooitje dus... en wie mag het weer oplossen?.... precies....

Ik wil dus dmv. een query die loze (orphan) records eruit hebben. Dus ik maakte vrolijk de volgende query:

SELECT Tabel2.Veld5, Tabel1.Veld2
FROM Tabel2 INNER JOIN Tabel1 ON Tabel2.Veld5 = tabel1.Veld2
WHERE (((Tabel2.Veld5)<>[Tabel1]![Veld2]));

Als resultaat krijg ik nul records terug, terwijl ik ZEKER WEET dat er in beide tabellen orphan records zijn! (ik heb ze als test zelfs extra aangemaakt).

Als ik dan de relatie tussen veld2 en veld5 weghaal krijg ik een query die
750000 keer 750000 records returned... ook niet goed dus.

WAT DOE IK FOUT???

Mijn SQL is dus niet van dusdanige kwaliteit dat ik dit zelf op kan lossen. Ik
ben overigens wel op zoek naar een goed boek over de taal SQL. Iemand een aanrader? Op amazon of Bol.com staan er genoeg, maar wat is een goeie...

Alvast bedankt!

[whoami]

Los het eens op met een subquery:

code:

select * from tabel2
where tabel2.fik not in (select primkey from tabel1)

[Theguide]

Orphans uit tabel1 weergeven:
SELECT * FROM tabel1 WHERE tabel1.veld2 NOT IN (SELECT table2.veld5 FROM table2);

Orphans uit tabel2 weergeven:
SELECT * FROM tabel2 WHERE tabel2.veld5 NOT IN (SELECT table1.veld2 FROM table1);

Tenminste... ik denk dat het zo kan... Laat maar even weten of het werkte, dan leer ik er ook nog wat van

[Dido]

Dat die eerste het niet doet is niet zo gek... je dooet een join op die velden (dus waar ze gelijk zijn) en dan selecteer je de resultaten wara dat niet zo is... Alsof je alle blauwe ballen uitzoekt, en daarvan alleen de gele wilt hebben.

Maar als je met subqueries kunt werken zou ik voor whoami's idee gaan.

[Verwijderd]

Dido schreef op 20 November 2002 @ 15:32:
Dat die eerste het niet doet is niet zo gek... je dooet een join op die velden (dus waar ze gelijk zijn) en dan selecteer je de resultaten wara dat niet zo is... Alsof je alle blauwe ballen uitzoekt, en daarvan alleen de gele wilt hebben.

ja, inderdaad, ik zie het nu. grappig!

ben ff de oplossing van Theguide aan het testen;

SELECT tabel1.*, Tabel1.Veld2
FROM tabel2, Tabel1
WHERE (((Tabel1.Veld2) Not In (SELECT tabel2.veld5 FROM tabel2)));

[Goodielover]

in de from clause moet je tabel 2 weglaten.
Tabel2 staat in de subquery. dan hoef je hem niet meer op te nemen in de hoofdquery

[Verwijderd]

Misschien vergeten te zeggen; de unieke velden zijn NIET de primary keys. Duurt het daarom zo lang (10min and counting). Die query zoekt nu volgens mij 75.000 keer tabel1.veld2 in 75000 records van tabel2.veld5

[whoami]

Tja, dat kan.... Liggen er indices op die velden?
10 minutn is wel heel lang.... Dat komt omdat je in je from clausule (als je die query van TheGuide gebruikt) zowel tabel1 als tabel2 staan hebt, en die nergens linkt in de where clause. Die tabel2 mag moet dus weg, want hij staat in de subquery)

[Goodielover]

Dankje whoami voor het herhalen van de boodschap.
Komt 'ie nu wel over?
ipv de not in, kan je er misschien beter een not exists van maken en de subquery dan gecorrelleerd ^{(schrijf je dat zo)} maken.

[Verwijderd]

Goodielover schreef op 20 november 2002 @ 16:10:
Dankje whoami voor het herhalen van de boodschap.
Komt 'ie nu wel over?

Ik heb natuurlijk ook jou raad meteen opgevolgd GoodieLover, thanx.

De query is nu

SELECT Tabel1.*, Tabel1.Veld2
FROM Tabel1
WHERE (((Tabel1.Veld2) Not In (SELECT Tabel2.veld5 FROM T_Q_flt_arn)));

en deze query loopt nu dus al een half uur. Ik ga eens proberen indices te leggen, op de velden 2 en 5.

UPDATE...

Staat nu een alweer een half uur te stampen, met indices op veld2 en veld5.
Ik laat het wel doorlopen tot morgenochtend, eens kijken offie dan klaar is.

[xtra]

Voor die niet SQL-kenners is er ook nog een wizard
In Access: Nieuwe query --> wizard niet-gerelateerde records.

[Goodielover]

Maak er eens van:

code:

SELECT *
FROM   Tabel1
WHERE  Not exists 
      (SELECT 1
       FROM Tabel2
       WHER Tabel2.veld5 = Tabel1.Veld2
      )

Als je dan een index hebt liggen op veld5 in Tabel2 dan moet dit snel gaan.

[akakiwi]

Of, je doet het met een LEFT OUTER JOIN, dan krijg je ze er ook zeker uit

code:

SELECT Tabel2.Veld5, Tabel1.Veld2
FROM Tabel2 
  LEFT OUTER JOIN Tabel1 
  ON Tabel2.Veld5 = tabel1.Veld2
WHERE (((Tabel2.Veld5)<>[Tabel1]![Veld2]));

[Goodielover]

Met een left outer join moet je niet de ongelijk gebruiken maar testen op Tabel2.Veld5 is null.
NULL <> '2' is namelijke formeel False

[Verwijderd]

Een ander soort oplossing:
Als je nou in je relationships window een relatie legt tussen Tabel1.Veld2 en Tabel2.Veld5, en je kruist "Enforce referential integrity" aan, dan geeft Access vervolgens zelf aan dat er records zijn die daar niet aan voldoen, en vraagt of ie die moet weggooien.
Het is dan misschien wel handig om indices te zetten op de betreffende velden...