[sql] Query moet ook tonen als records niet bestaan

[AW_Bos]

Ik heb deze prachtige query hier gemaakt, maar hij toont niet alles uit de review-tabel. Dit lijkt te komen doordat de query verwacht dat er een ID-koppeling tussen r.id en u.KindID moet bestaan.

De query op dit moment is dit:

SQL:

SELECT
r.id,
r.title,
r.author,
r.date_posted,
r.review_short,
r.review,
r.embargo,
r.has_photogallery,
r.deleted, 
u.has_thumbnail, 
u.filename,
u.KindID

FROM
    reviews r
LEFT JOIN
    uploads u ON r.id = u.KindID
WHERE
    r.embargo = '0'
AND
    r.deleted = '0'
AND
    (u.has_thumbnail = '1' OR u.has_thumbnail IS NULL) 
AND Uploadtype_ID = '3'

Wat is het
Nou, het is een tabel waarin ik alle image-uploads met de filenames heb staan gekoppeld per categorie en het ID van het item zelf.

Werking
De bedoeling van de laatste statement zal ik even uitleggen:
Ik heb een JOIN met de tabel uploads, waarin de paden van de geuploade bestanden staan. Elk record kent een veld met daarin Uploadtype_ID (welke uploadcategorie is dit (stationspaginainfo, reviews) en KindID (welk ID van de review/stationspaginainfo is het?) )
De u.has_thumbnail geeft aan dat er bij dit item de gekozen bestand moet worden gebruikt als thumbnail. Deze is vaak een 1, maar kan ook een 0 zijn. Maar hij kan ook niet bestaan, want niet elke review heeft altijd uploads.

Voorbeeld
Dus als in de query als resultaat Uploadtype_ID de integer 3 is, en KindID 17, dan gaat het dus over bijv. 'Rondleiding NedTrain Haarlem'.

Maar goed, genoeg gepraat over de werking van de tabel, het werkt dus niet, en ik krijg alleen alles te zien wat gekoppeld is, niet wat niet gekoppeld is en niet bestaat

Wie-o-wie kan me helpen, ik hoop dat het hele verhaal duidelijk is. Ik heb al diverse dingen geprobeerd zoals de IS NULL weghalen de controles aanpassen met een OR en filteren op u.has_thumbnail=1 en op 0

Ik kom er niet uit, en zit al uren te turen naar dit complex-lijkende vraagstuk welke blijkbaar achteraf misschien toch best wel simpel zou zijn.

[Asator]

Uploadtype_ID staat ook in de uploads table neem ik aan? Daarop gaat hij waarschijnlijk fout.
Als je hem aanpast dat IS NULL waardes ook toegestaan zijn, net als bij u.has_thumbnail, dan zou het wel alle records terug moeten geven.

[Keiichi]

inner join?

[AW_Bos]

Uploadtype_ID staat in Uploads, maar is niets meer dan een kenmerk voor de soort upload.
Een KindID kan dubbel zijn en kan net zo goed ook bij stationinformatie of spoormaterieel horen. Daarom dat onderscheid met Uploadtype_ID, ik had het net zo goed gewoon VARCHAR met 'reviews' kunnen noemen.

[AW_Bos]

Keiichi schreef op woensdag 30 januari 2013 @ 22:53:
inner join?

Als ik de join aanpas naar:

code:

INNER JOIN uploads u ON r.id = u.KindID

.. dan blijft hij alleen alles selecteren waarbij er een koppeling is. Terwijl er ook geen bijbehorende items in de uploadstable kunnen staan. M.a.w. het is dus niet verplicht om een upload te hebben bij een review.

[DNB]

AW_Bos schreef op woensdag 30 januari 2013 @ 22:55:
Uploadtype_ID staat in Uploads, maar is niets meer dan een kenmerk voor de soort upload.
Een KindID kan dubbel zijn en kan net zo goed ook bij stationinformatie of spoormaterieel horen. Daarom dat onderscheid met Uploadtype_ID, ik had het net zo goed gewoon VARCHAR met 'reviews' kunnen noemen.

Maar, zoals Asator zegt, als er geen upload is bij een review, is er ook geen Uploadtype_ID. (Dus NULL)
Je vraagt om een resultaat waar dit 3 is. Als er geen upload is bij een review wordt die review dan ook niet weergegeven (want Uploadtype_ID is daar NULL, en niet 3 zoals je eist in de query).
Volgens mij zou Asator's oplossing moeten werken voor jouw probleem.

[DataGhost]

Werkt het ook zonder "AND (u.has_thumbnail = '1' OR u.has_thumbnail IS NULL) AND Uploadtype_ID = '3'"? Volgens mij sluit je daarmee namelijk alle reviews zonder uploads uit, aangezien bijv. Uploadtype_ID nooit '3' zal zijn voor een review zonder upload. Vandaar dat een extra check op Uploadtype_ID IS NULL het waarschijnlijk zal fixen, zoals Asator bedoelde.

edit:
Ja wat hij zegt dus ^^

[ Voor 5% gewijzigd door DataGhost op 30-01-2013 23:07 ]

[semitweakert]

AW_Bos schreef op woensdag 30 januari 2013 @ 21:51:
Maar goed, genoeg gepraat over de werking van de tabel, het werkt dus niet, en ik krijg alleen alles te zien wat gekoppeld is, niet wat niet gekoppeld is en niet bestaat

Ik heb je post nu een paar keer gelezen, maar het ontgaat me een beetje wat je nu precies wilt.. Je wilt niet alles van de review tabel laten zien (dat blijkt wel uit de where clausules), maar wat je wel wil laten zien is me dus niet duidelijk.

AW_Bos schreef op woensdag 30 januari 2013 @ 21:51:
De u.has_thumbnail geeft aan dat er bij dit item de gekozen bestand moet worden gebruikt als thumbnail. Deze is vaak een 1, maar kan ook een 0 zijn. Maar hij kan ook niet bestaan, want niet elke review heeft altijd uploads.

Je geeft hier aan dat u.has_thumbnail 0 of 1 kan zijn, of niet kan bestaan, maar je checkt op 1 of NULL

Misschien kun je je probleem nog iets verduidelijken?

[AW_Bos]

DNB schreef op woensdag 30 januari 2013 @ 23:06:
[...]

Maar, zoals Asator zegt, als er geen upload is bij een review, is er ook geen Uploadtype_ID. (Dus NULL)
Je vraagt om een resultaat waar dit 3 is. Als er geen upload is bij een review wordt die review dan ook niet weergegeven (want Uploadtype_ID is daar NULL, en niet 3 zoals je eist in de query).
Volgens mij zou Asator's oplossing moeten werken voor jouw probleem.

Als er geen upload is bij ene review, dan is er geen record die erbij hoort. En daar moet ik ergens een contole voor hebben.
Maar hoe, INNER JOIN werd me al ingefluisterd, maar dat werkt nog niet....

Ik wil gewoon alle reviews zien uit de review tabel, echter in de JOIN is een koppeling met de uploads, soms zijn er wel uploads, soms ook niet. En daar wil ik op controleren. Een review hoeft dus niet altijd een upload te hebben.

[ Voor 15% gewijzigd door AW_Bos op 30-01-2013 23:20 ]

[RobIII]

SQL:

...
LEFT JOIN uploads u ON r.id = u.KindID AND Uploadtype_ID = '3'
...

Uit het hout dat ik van je topic kan snijden zou ik zoiets gedaan hebben vermoedelijk (en dus die Uploadtype_ID uit de where clause halen)

...En moet 't niet 3 zijn i.p.v. '3'?

[ Voor 25% gewijzigd door RobIII op 30-01-2013 23:28 ]

[AW_Bos]

Ik geloof dat ik hem dan heb... In ieder geval iets wat beter werkt dan wat het eerst was

SQL:

SELECT r.id, r.title, r.author, r.date_posted, r.review_short, r.review, r.embargo, r.has_photogallery, r.deleted, u.has_thumbnail, u.filename, u.KindID
FROM reviews r
LEFT JOIN uploads u ON r.id = u.KindID AND Uploadtype_ID =3 
AND Uploadtype_ID = 3
WHERE r.embargo = '0'
AND r.deleted = '0'
AND (
u.has_thumbnail = '1'
OR u.has_thumbnail IS NULL
)

Heb alleen het idee dat hij de r.embargo en r.deleted negeert. Maar dat zoek ik morgen wel even uit.

[RobIII]

AW_Bos schreef op woensdag 30 januari 2013 @ 23:30:

SQL:

LEFT JOIN uploads u ON r.id = u.KindID AND Uploadtype_ID = '3' 
AND Uploadtype_ID = 3

Euh... AND Uploadtype_ID = '3' AND Uploadtype_ID = 3 Pick one and stick with it
Waarom heb je sowieso alle integers tussen quotes staan?

De u.has_thumbnail = '1' OR u.has_thumbnail IS NULL kan waarschijnlijk op dezelfde manier naar de left join clause worden verplaatst:

SQL:

... 
LEFT JOIN uploads u ON r.id = u.KindID AND Uploadtype_ID = 3 AND u.has_thumbnail = 1
...

[ Voor 34% gewijzigd door RobIII op 30-01-2013 23:33 ]

[Douweegbertje]

AW_Bos schreef op woensdag 30 januari 2013 @ 23:30:
Ik geloof dat ik hem dan heb... In ieder geval iets wat beter werkt dan wat het eerst was

SQL:

SELECT r.id, r.title, r.author, r.date_posted, r.review_short, r.review, r.embargo, r.has_photogallery, r.deleted, u.has_thumbnail, u.filename, u.KindID
FROM reviews r
LEFT JOIN uploads u ON r.id = u.KindID AND Uploadtype_ID =3 
AND Uploadtype_ID = 3
WHERE r.embargo = '0'
AND r.deleted = '0'
AND (
u.has_thumbnail = '1'
OR u.has_thumbnail IS NULL
)

Heb alleen het idee dat hij de r.embargo en r.deleted negeert. Maar dat zoek ik morgen wel even uit.

dan is het toch nog steeds dit?

code:

SELECT r.id, r.title, r.author, r.date_posted, r.review_short, r.review, r.embargo, r.has_photogallery, r.deleted, u.has_thumbnail, u.filename, u.KindID
FROM reviews AS r
LEFT JOIN uploads AS u ON r.id = u.KindID 
WHERE r.embargo = '0'
AND r.deleted = '0'
AND Uploadtype_ID = '3'
AND (u.has_thumbnail = '1' OR u.has_thumbnail IS NULL)

[AW_Bos]

douweegbertje schreef op woensdag 30 januari 2013 @ 23:39:
[...]


dan is het toch nog steeds dit?

code:

SELECT r.id, r.title, r.author, r.date_posted, r.review_short, r.review, r.embargo, r.has_photogallery, r.deleted, u.has_thumbnail, u.filename, u.KindID
FROM reviews AS r
LEFT JOIN uploads AS u ON r.id = u.KindID 
WHERE r.embargo = '0'
AND r.deleted = '0'
AND Uploadtype_ID = '3'
AND (u.has_thumbnail = '1' OR u.has_thumbnail IS NULL)

Die toont alleen de reviewsl waar een upload bij is. Ik heb Rob's query.
Zit alleen nog iets vaags in waarbij iets mist, maar dat ga ik morgen even uitzoeken, anders heb ik wel een workaround in gedachten

[DataGhost]

AW_Bos schreef op woensdag 30 januari 2013 @ 23:18:
[...]

Maar hoe, INNER JOIN werd me al ingefluisterd, maar dat werkt nog niet....

Ik zou als ik jou was eerst even uitzoeken wat de verschillen tussen de verschillende JOINs zijn voordat je lukraak wat gaat doen. Straks werkt het per ongeluk een keer en dan heb je nog steeds geen flauw idee waarom. Ik kan je in ieder geval vertellen dat het met een INNER JOIN zeker weten niet gaat doen wat jij wilt. Het verplaatsen van zooi uit de WHERE- naar de ON-clause kan je als syntactische sugar zien voor je checks op IS NULL (als ik me niet samen met RobIII vergis) dus dat zou inderdaad ook je probleem op moeten lossen maar het lijkt me beter als je doorkrijgt waarom dat zo is .

[AW_Bos]

Ga ik eens naar kijken morgen. Ik meen dat INNER JOIN ook velden koppelde waarvan ze niet zouden hoeven te bestaan, waarmee ze een NULL kregen. Maar dat gold dus voor LEFT JOIN blijkbaar.

De kennis van JOINS is duidelijk een beetje weggesleten bij mij, merk ik

[T-8one]

AW_Bos schreef op woensdag 30 januari 2013 @ 23:30:
Ik geloof dat ik hem dan heb... In ieder geval iets wat beter werkt dan wat het eerst was

SQL:

SELECT r.id, r.title, r.author, r.date_posted, r.review_short, r.review, r.embargo, r.has_photogallery, r.deleted, u.has_thumbnail, u.filename, u.KindID
FROM reviews r
LEFT JOIN uploads u ON r.id = u.KindID AND Uploadtype_ID =3 
AND Uploadtype_ID = 3
WHERE r.embargo = '0'
AND r.deleted = '0'
AND (
u.has_thumbnail = '1'
OR u.has_thumbnail IS NULL
)

Heb alleen het idee dat hij de r.embargo en r.deleted negeert. Maar dat zoek ik morgen wel even uit.

Door je filter op Uploadtype_D (welke naar ik begrijp uit uploads table komt) worden de resultaten uit upload welke niet voorkomen in reviews table weggefilterd. Door de outerjoin is Uploadtype_D namelijk leeg omdat die dus niet in upload voorkomt.
Zet deze filter in een subquery voor de uploads table en je probleem is volgens mij opgelost.

Joins:

[ Voor 3% gewijzigd door T-8one op 31-01-2013 16:47 ]

[AW_Bos]

Jep, uiteindelijk is het dus dit geworden:

SQL:

SELECT r.id, r.title, r.author, r.date_posted, r.review_short, r.review, r.embargo, r.has_photogallery, r.deleted, u.has_thumbnail, u.filename, u.KindID
FROM reviews r
LEFT JOIN uploads u ON r.id = u.KindID
AND Uploadtype_ID =3
AND (
u.has_thumbnail = '1'
OR u.has_thumbnail IS NULL
)
AND Uploadtype_ID =3
WHERE r.embargo = '0'
AND r.deleted = '0' 

De thumnail-statement heb ik verschoven naar de ON-clausule en nu werkt het wel goed.
(en dat zelfs zonder subquery, waar ik ook even aan zat te denken)

[ Voor 6% gewijzigd door AW_Bos op 31-01-2013 18:00 ]

[RobIII]

En nog steeds heb je (vermoedelijk) onnodig quotes om je integers staan, nog steeds heb je niet aangegeven in welke tabel Uploadtype_ID nou voorkomt (wat we allemaal maar aannemen is dat 't in de uploads tabel staat) en nog steeds gebruik je een OR u.has_thumbnail IS NULL die waarschijnlijk ook naar de join-clause kan. En nog steeds heb je twee keer een AND Uploadtype_ID =3 in je query staan; ik denk dat je RDBMS 't na een keer wel begrepen heeft, het RDBMS zal niet doof zijn (Ja, de queryplanner zal dit waarschijnlijk toch wel wegoptimaliseren, maar met MySQL weet je 't nooit ).

[AW_Bos]

Uploadtype_ID staat in de upload-table en is verder geen gekoppeld veld. Het had net zo goed een E_NUM met 'stations', 'reviews','etc..' kunnen zijn. Maar ik heb nu even voor een integer gekozen. Mogelijk dat ik ze later ga koppelen met een andere tabel.

En die quotes en die dubbele voorwaarde zal ik ook nog even fixxen voordat ik het live knal.

[ Voor 37% gewijzigd door AW_Bos op 31-01-2013 19:21 ]