[MySQL] Missende links in relationele database zoeken.

[cyberstalker]

Ik heb een database waarin een aantal entries mist, waarnaar wordt verwezen vanuit een andere tabel.

Het gaat om de tabellen topics en users. In de users tabel missen een aantal entries. De layout is als volgt:

users:

SQL:

+----------+-----------------+------+-----+---------+-------+
| Field    | Type            | Null | Key | Default | Extra |
+----------+-----------------+------+-----+---------+-------+
| id       | int(4) unsigned |      | PRI | 0       |       |
| username | varchar(30)     | YES  | MUL | NULL    |       |
+----------+-----------------+------+-----+---------+-------+

topics:

SQL:

+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(3) unsigned  |      | PRI | 0       |       |
| forum    | int(1) unsigned  |      |     | 0       |       |
| title    | varchar(60)      | YES  | MUL | NULL    |       |
| postIcon | int(1) unsigned  |      |     | 0       |       |
| userId   | int(3) unsigned  |      | MUL | 0       |       |
| postTime | int(4) unsigned  |      |     | 0       |       |
| clicks   | int(10) unsigned |      | MUL | 0       |       |
| last_ip  | int(4) unsigned  |      |     | 0       |       |
+----------+------------------+------+-----+---------+-------+

userId in de tabel topics verwijst naar een id in users. In users mis ik echter zo'n tienduizend entries. Ik probeer daar een lijst van te krijgen, zodat ik deze kan aanvullen.

Iets zegt mij dat ik daar een subquery voor nodig heb, en dat heb ik dan ook geprobeerd, maar ik begrijp het niet, en ik doe iets verkeerd.

SQL:

SELECT DISTINCT userId FROM topics WHERE (SELECT COUNT(id) FROM users WHERE users.id=topics.userId)=0;

Ik krijg hier dus een foutmelding van MySQL. Is er iemand die mij kan uitleggen wat ik fout doe?

[whoami]

Nee, want je zegt niet welke foutmelding je krijgt.

Verder klopt je SQL query zowiezo niet.
Als je de users wil hebben die zich in de topics tabel bevinden, maar niet in de users tabel, dan heb je idd een subquery nodig (ondersteunt jouw versie van mysql dat al ? Zoniet kan je mbhv een outer join hetzelfde resultaat krijgen).

code:

select userid from topcis
where userid not in ( select id from users )

Kent de versie van MySQL waar je mee werkt dan geen foreign key constraints ?

[cyberstalker]

OK, hier dan de foutmelding:

SQL:

select distinct userId from topics where (select count(id) from users where users.id=topics.userId)=0;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select count(id) from users where users.id=topics.userId)=0' at

De serverversie is overigens '4.0.23_Debian-3ubuntu2.1-log'. Ik heb ook jouw suggestie geprobeerd (zonder de spelfouten ), maar die geeft mij ook een error:

SQL:

select userId from topics where userId not in (select id from users);
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select id from users)' at line 1

[NMe]

MySQL ondersteunt pas vanaf versie 4.1 subqueries.

[whoami]

Tja, dan zal je het dus op een andere manier moeten oplossen.
Met een outer join.
Ik geloof dat er in de mysql manual wel uitgelegd staat hoe je dat kunt gebruiken ipv subqueries.

[GX]

Zou

SQL:

SELECT    a.userId
FROM      topics a
LEFT JOIN users b
  ON     (a.userId = b.id)
WHERE     b.id = NULL

niet werken? Het is dan wel psuedo want ik heb even de mogelijkheid niet om te testen.

[ Voor 4% gewijzigd door GX op 31-10-2005 09:47 ]

[Grijze Vos]

GX schreef op maandag 31 oktober 2005 @ 09:46:
Zou

SQL:

SELECT    a.userId
FROM      topics a
LEFT JOIN users b
  ON     (a.userId = b.id)
WHERE     b.id = NULL

niet werken? Het is dan wel psuedo want ik heb even de mogelijkheid niet om te testen.

Als b.id = NULL, dan is ook a.userID = NULL, en dan krijg je een ontzettend groot cartesisch product denk ik zo.

---------
Als ik het goed begrijp heeft de TS een tabel1 met verwijzingen naar tabel2, waarbij de elementen uit tabel2 er niet meer zijn. Wat ik zou doen, is even het maximum ID queryen van de FK van tabel1 en de PK van tabel2. Dan maak je een composite (is dat het juiste woord?) tabel van alle ID's tot en met het maximum van tabel2, en kun je daarmee joinen.

Je zou dat in 1 query kunnen doen, maar ik zou het simpel houden en gewoon ff een temp tabelletje aanleggen..

[GX]

Grijze Vos schreef op maandag 31 oktober 2005 @ 14:54:
[...]


Als b.id = NULL, dan is ook a.userID = NULL, en dan krijg je een ontzettend groot cartesisch product denk ik zo.

Volgens mij niet; het is namelijk een left join, dus alleen de items uit tabel b die NULL zijn hebben een NULL value, de linker kant heeft dan een kloppende ID welke dus rechts mist.

[Grijze Vos]

GX schreef op maandag 31 oktober 2005 @ 14:56:
[...]

Volgens mij niet; het is namelijk een left join, dus alleen de items uit tabel b die NULL zijn hebben een NULL value, de linker kant heeft dan een kloppende ID welke dus rechts mist.

Je join-conditie vraagt toch om gelijkheid?

--------------
Dit is volgens mij wat TS bedoelt:

code:

tabel1
id | FK
1  | 1
2  | 2
3  | 3
4  | 4
5  | 5

tabel2
PK
1
3
5

De bedoeling is dus om nu de id's 2 en 4 terug te krijgen, de missende PKs uit tabel2, die vanuit tabel1 gevraagd worden.

[ Voor 29% gewijzigd door Grijze Vos op 31-10-2005 15:00 ]

[cyberstalker]

Grijze Vos schreef op maandag 31 oktober 2005 @ 14:57:
[...]

Je join-conditie vraagt toch om gelijkheid?

--------------
Dit is volgens mij wat TS bedoelt:

code:

tabel1
id | FK
1  | 1
2  | 2
3  | 3
4  | 4
5  | 5

tabel2
PK
1
3
5

De bedoeling is dus om nu de id's 2 en 4 terug te krijgen, de missende PKs uit tabel2, die vanuit tabel1 gevraagd worden.

Dat is precies wat ik bedoel. De suggestie van GX werkt overigens niet, ik krijg dan een 'Empty set' terug.

Het is ook niet zo dat de gegevens na een bepaald punt missen, er zitten dus zeg maar 'gaten' in de tabel users.

Ik heb net een tutorial over left join's gelezen, en voor zover ik begrijp is de query van GX inderdaad correct, maar toch krijg ik niet de resultaten die ik wil, ik probeerde het volgende:

SQL:

SELECT
    a.userId
FROM
    topics  AS  a
LEFT JOIN
    users   AS  b
ON
    (a.userId = b.id)
WHERE
    b.id=NULL;

[ Voor 6% gewijzigd door cyberstalker op 31-10-2005 15:43 . Reden: spelvautjes ]

[TheRookie]

cyberstalker schreef op maandag 31 oktober 2005 @ 15:42:
maar toch krijg ik niet de resultaten die ik wil

Wat krijg je dan wel/niet terwijl je het wel/niet verwacht ?

[cyberstalker]

TheRookie schreef op maandag 31 oktober 2005 @ 16:06:
[...]

Wat krijg je dan wel/niet terwijl je het wel/niet verwacht ?

Ik krijg dus een empty set, zoals ik al zei in mijn vorige bericht. En dat klopt niet, omdat er meer dan negenduizend entries missen in de users tabel.

[GX]

Grijze Vos schreef op maandag 31 oktober 2005 @ 14:57:
[...]

Je join-conditie vraagt toch om gelijkheid?

Nee, mijn join-conditie vraagt om gelijkheid-of-geen-record

[Olaf van der Spek]

cyberstalker schreef op maandag 31 oktober 2005 @ 15:42:
b.id=NULL

Is dat niet per definitie false? 'is null' werkt beter gok ik.

[cyberstalker]

OlafvdSpek schreef op maandag 31 oktober 2005 @ 17:00:
[...]

Is dat niet per definitie false? 'is null' werkt beter gok ik.

Dat ik daar zomaar overheen kijk .