vendredi 10 juillet 2015

SQL/SQLite: unexpected amount of results despite using DISTINCT

I have ~8000 entries in table "adr" and want to join it with some other tables. However, this leads to 200k+ results despite using DISTINCT. The culprit here is "stort", as the second query works as expected. However, I can't figure out what I'm doing wrong with the first one. Is it the double gemeinde_id?

200k+ results:

SELECT DISTINCT str.name, adr.hausnummer, stort.name, plz.postleitzahl, adr.PKUID
FROM adresse adr, strasse str, gemeinde gem, stadt_ortsteil stort, postleitzahl plz
WHERE adr.strasse_id = str.strasse_id AND adr.postleitzahl_id = plz.postleitzahl_id AND str.gemeinde_id = gem.gemeinde_id AND gem.gemeinde_id = stort.gemeinde_id

8000 results:

SELECT DISTINCT str.name, adr.hausnummer, gem.name, plz.postleitzahl, adr.PKUID
FROM adresse adr, strasse str, gemeinde gem, postleitzahl plz
WHERE adr.strasse_id = str.strasse_id AND adr.postleitzahl_id = plz.postleitzahl_id AND str.gemeinde_id = gem.gemeinde_id

Aucun commentaire:

Enregistrer un commentaire