PHP / MySQL query met Left Join geeft 1 waarde minder

[The.Terminator]

Leute,

Ik heb eerst een query gebruikt die adverts liet zien doormiddel van onderstaande query.

code:

SELECT id, title, views, isvisible, date FROM adverts WHERE categoryid='1' AND isvisible='1' ORDER BY date DESC

> 12 resultaten

Maaaaaar... Hier wou ik ook het aantal biedingen erbij en dit werkt in principe door gebruik te maken van LEFT JOIN

code:

SELECT adverts.id, adverts.title, adverts.views, adverts.isvisible, adverts.date, COUNT(biddings.id) as bids 
FROM adverts 
LEFT JOIN biddings 
ON adverts.id=biddings.advertid 
WHERE adverts.categoryid='1' AND adverts.isvisible='1' AND biddings.activationid IS NULL GROUP BY adverts.id ORDER BY date DESC

Dit werkt in principe ook, alleen krijg ik hier 11 resultaten, waar ik er eigenlijk 12 moet krijgen.
Enig idee waarom de laatste niet wordt weergegeven ?

[RobIII]

Je left joined en vervolgens laat je er een where op los (en stel je dus voorwaarden)

AND biddings.activationid IS NULL

3x raden hoe dat "verdwenen" record er uit ziet

En wat creepy hieronder zegt over de group by idd.

[ Voor 46% gewijzigd door RobIII op 03-06-2008 22:07 ]

[Creepy]

Je hebt een extra deel in je where. Kan het zijn dat de missende een activationid heeft die null is?
Edit: snelle Rob

En tip: kijk ook eens naar je group by want alleen MySQL staat dit toe, elk ander DMBS niet.

[ Voor 4% gewijzigd door Creepy op 03-06-2008 22:02 ]

[michelzwarts]

Het is een left join, alle resultaten (>12) zouden dus moeten worden getoond...

[RobIII]

michelzwarts schreef op dinsdag 03 juni 2008 @ 22:06:
Het is een left join, alle resultaten (>12) zouden dus moeten worden getoond...

Nee, het is een left join met een WHERE clause. There's a difference

[michelzwarts]

RobIII schreef op dinsdag 03 juni 2008 @ 22:07:
[...]

Nee, het is een left join met een WHERE clause. There's a difference

De additionele where clause is op de detail table..?

[RobIII]

michelzwarts schreef op dinsdag 03 juni 2008 @ 22:08:
[...]


De additionele where clause is op de detail table..?

Ik zie geen "detail" tabel?
RobIII in "PHP / MySQL query met Left Join geeft 1 ..."

[michelzwarts]

RobIII schreef op dinsdag 03 juni 2008 @ 22:11:
[...]

Ik zie geen "detail" tabel?
RobIII in "PHP / MySQL query met Left Join geeft 1 ..."

Ben geen SQL goeroe, maar de additionele where gaat over biddings. Ook al is die null/unmatched zou toch de advert vanwege de left join getoond moeten worden?

[Confusion]

michelzwarts schreef op dinsdag 03 juni 2008 @ 22:12:
Ben geen SQL goeroe, maar de additionele where gaat over biddings. Ook al is die null/unmatched zou toch de advert vanwege de left join getoond moeten worden?

Niet ook, maar alleen als de bijbehorende bidding.activationid NULL is. Als de advertid gematched is, en de activationid != NULL, dan wordt de rij niet getoond.

[ Voor 10% gewijzigd door Confusion op 03-06-2008 22:16 ]

[RobIII]

Nee, die wordt eruit gehaald omdat 'ie (alsnog) niet voldoet aan de WHERE.

edit:

Damn, gaat hard
Wat hierboven dus gezegd wordt

[ Voor 32% gewijzigd door RobIII op 03-06-2008 22:16 ]

[The.Terminator]

Tis MySQL :-P

Maar wat RobIII zegt, het missende record heeft een bidding die nog gevalideerd moet worden (dus heeft een activationid ingevuld.

Dus als ik gebruik maak van

code:

SELECT adverts.id, adverts.title, adverts.views, adverts.isvisible, adverts.date, COUNT(biddings.id) as bids 
FROM adverts 
LEFT JOIN biddings 
ON adverts.id=biddings.advertid 
WHERE adverts.categoryid='1' AND adverts.isvisible='1' GROUP BY adverts.id ORDER BY date DESC

Dan laat die er weer 12 zien met het missende record
Maar dan zit ik met het probleem dat voor die advert een bod is gedaan die nog niet gevalideerd is

Hij laat nu dus zien

Advertentie X heeft 1 bod

Terwijl er moet staan

Advertentie X heeft 0 (geen) bod

Omdat het bod nog niet is gevalideerd

[Patriot]

Niet zoals het daar in de WHERE-clause staat, zet "AND biddings.activationid IS NULL" eens in de ON-clause.

EDIT: Beetje half spuit 11

[ Voor 16% gewijzigd door Patriot op 03-06-2008 22:17 ]

[michelzwarts]

Confusion schreef op dinsdag 03 juni 2008 @ 22:15:
[...]

Niet ook, maar alleen als de bijbehorende bidding.activationid NULL is. Als de advertid gematched is, en de activationid != NULL, dan wordt de rij niet getoond.

Wat is hier dan het nut van een left join tov een inner join?

Scratch this. Weer iets bijgeleerd vandaag

[ Voor 6% gewijzigd door michelzwarts op 03-06-2008 22:20 ]

[whoami]

michelzwarts schreef op dinsdag 03 juni 2008 @ 22:12:
[...]


Ben geen SQL goeroe, maar de additionele where gaat over biddings. Ook al is die null/unmatched zou toch de advert vanwege de left join getoond moeten worden?

Dat zal ie alleen doen, als je dat extra criterium in je ON clausule opneemt ...
/laat dus

wat meer uitleg:
Je doet een OUTER JOIN op je 2 tabellen; voor de records in adverts die geen biddings hebben, wordt dit in je resultset geplaatst:

advertid biddingid activationid
1 NULL NULL

De WHERE gaat dan blijkbaar die set gaan evalueren; als jij dan zegt 'WHERE biddingid = 1', dan wordt NULL == 1 ? natuurlijk als false ge-evalueert.

michelzwarts schreef op dinsdag 03 juni 2008 @ 22:17:
[...]


Wat is hier dan het nut van een left join tov een inner join?

Stel dat je een INNER JOIN doet op de tabel 'klant' die gekoppeld is met een tabel adres.
Als een klant geen adres heeft, zal deze klant niet in je resultset komen.
Als je dit met een OUTER JOIN doet zal die klant wel in je resultset komen.

[ Voor 55% gewijzigd door whoami op 03-06-2008 22:23 ]

[RobIII]

michelzwarts schreef op dinsdag 03 juni 2008 @ 22:17:
[...]


Wat is hier dan het nut van een left join tov een inner join?

Een join doe je _doorgaans op (foreign) keys; dat staat los van je where clause. De bedoeling is wel duidelijk, TS schrijft het dus alleen verkeerd uit. Er worden hier netjes keys gebruikt om op te joinen, maar de left join wordt (deels) teniet gedaan door de where-clause. * RobIII even moeite heeft dit helder uit te leggen Misschien dat dit helpt: Hoe werken joins?

[ Voor 16% gewijzigd door RobIII op 03-06-2008 23:30 ]

[michelzwarts]

RobIII schreef op dinsdag 03 juni 2008 @ 22:19:
[...]

Een join doe je op (foreign) keys; dat staat los van je where clause. De bedoeling is wel duidelijk, TS schrijft het dus alleen verkeerd uit. Er worden hier netjes keys gebruikt om op te joinen, maar de left join wordt (deels) teniet gedaan door de where-clause. * RobIII even moeite heeft dit helder uit te leggen

Thanks voor de 101. Persoonlijk heb ik ook meer met MSSQL gewerkt, maar je moet heel goed opletten waar je wat zet idd.

[whoami]

Dit geldt in MSSQL even goed

[michelzwarts]

whoami schreef op dinsdag 03 juni 2008 @ 22:23:
Dit geldt in MSSQL even goed

Klopt. Had zelf ook iets langzamer moeten lezen ;-)

[Confusion]

RobIII schreef op dinsdag 03 juni 2008 @ 22:19:
Een join doe je op (foreign) keys; dat staat los van je where clause.

Nou ja, een join, inner of outer, left of right, kan je altijd vervangen door een gelijkwaardige where clause.

[pedorus]

Hoe vervang je een left/right/outer join door een where clause? (Of bedoel je met gebruik union?)

Een join kun je natuurlijk best op niet-keys doen, maar de performance is dan vaak wat minder. In de faq wordt bij natural join ook zo'n aanname gemaakt. Een natural join kijkt echter gewoon simpelweg naar gelijke namen. Alleen daarom al is het handig om een kolom niet "id" te noemen zoals hier gebeurd, maar gewoon iets als "biddingId". (Dan zou je hier bijvoorbeeld een natural left join op een view activatedBiddings kunnen doen.)

[RobIII]

pedorus schreef op dinsdag 03 juni 2008 @ 23:19:
Hoe vervang je een left/right/outer join door een where clause?

Door in de FROM meer dan 1 tabel op te geven en ze te "joinen" in de WHERE clause.

quote: http://en.wikipedia.org/wiki/Join_(SQL)

SQL:2003 specifies two different syntactical ways to express joins. The first, called "explicit join notation", uses the keyword JOIN, whereas the second uses the "implicit join notation". The implicit join notation lists the tables for joining in the FROM clause of a SELECT statement, using commas to separate them. Thus, it specifies a cross-join, and the WHERE clause may apply additional filter-predicates. Those filter-predicates function comparably to join-predicates in the explicit notation.

Persoonlijk vind ik die manier gewoon minder expliciet en slecht(er) leesbaar.

pedorus schreef op dinsdag 03 juni 2008 @ 23:19:
Een join kun je natuurlijk best op niet-keys doen

Want...

pedorus schreef op dinsdag 03 juni 2008 @ 23:19:
Een natural join kijkt echter gewoon simpelweg naar gelijke namen.

quote: http://gathering.tweakers...sages/1105378#naturaljoin

Dat zal bijvoorbeeld de primary key van de ene tabel zijn die matched op een foreign key in de andere tabel of simpelweg een lijst van alle kolommen met eenzelfde naam.

Hoe dan ook vind ik natural joins ook niet the way to go, juist omdat ze impliciet zijn en niet expliciet aangeven op welke voorwaarde je wil joinen; dat gaat sooner-or-later geheid een keer mis

quote: http://en.wikipedia.org/wiki/Join_%28SQL%29#Natural_join

Using the NATURAL JOIN keyword to express joins can suffer from ambiguity at best, and leaves systems open to problems if schema changes occur in the database. For example, the removal, addition, or renaming of columns changes the semantics of a natural join. Thus, the safer approach involves explicitly coding the join-condition using a regular inner join.

[ Voor 93% gewijzigd door RobIII op 03-06-2008 23:37 ]

[pedorus]

RobIII schreef op dinsdag 03 juni 2008 @ 23:23:
Door in de FROM meer dan 1 tabel op te geven en ze te "joinen" in de WHERE clause.

Dan krijg je een inner/cross join.

Ik beweer dus dat het bovenstaande stukje FAQ over natural join niet klopt of niet duidelijk is, een natural join doet namelijk niks met informatie over keys. Probeer het maar uit, of google even. (Het komt nu een beetje over alsof een natural join iets magisch is.)

Of een natural join handig werkt hangt een beetje ervan af of de database-designer er goed rekening mee houdt. Je moet uitkijken dat een natural join tussen tabellen niet gebroken wordt bij een wijziging (bijvoorbeeld door twee tabellen een foreign key naar dezelfde tabel te laten krijgen). Aan de andere kant is het makkelijker om een key te wijzigen.

MySQL kent ook USING (...), wat op hetzelfde principe werkt, en dat probleem niet heeft.

[curry684]

RobIII schreef op dinsdag 03 juni 2008 @ 23:23:
[...]

Door in de FROM meer dan 1 tabel op te geven en ze te "joinen" in de WHERE clause.

Persoonlijk vind ik die manier gewoon minder expliciet en slecht(er) leesbaar.

Ik prefereer dan ook de term 'legacy' voor die schrijfmethode. Hij is verouderd en zeer slecht lees- en onderhoudbaar, met name als je ook nog eens Oracle-specific syntaxis erin gaat knikkeren voor outer joins.

[...]

Want...

Hij zei dat het kan. Dat klopt. Onwenselijk, maar het kan

[Bozozo]

Mierenneukmodus:

SELECT ... adverts.isvisible ... WHERE ... adverts.isvisible='1' ...;

...heeft niet zo veel nut

[Confusion]

pedorus schreef op woensdag 04 juni 2008 @ 00:00:
Dan krijg je een inner/cross join.

Een cross join is

SQL:

select a.foo, b.bar from a, b 

Dat is niet wat Rob schreef; die had het over:

SQL:

select a.foo, b.bar from a, b where a.baz = b.fuz

en dat is identiek aan

SQL:

select a.foo, b.bar from a join b on (a.baz = b.fuz) 

[pedorus]

De vorige post snap ik, maar ik vroeg me juist af hoe het met niet-inner/cross zat (mijn nadruk):

Confusion schreef op dinsdag 03 juni 2008 @ 22:32:
[...]

Nou ja, een join, inner of outer, left of right, kan je altijd vervangen door een gelijkwaardige where clause.

Dus hoe vervang je

SQL:

select a.foo, b.bar from a left join b on a.baz = b.fuz

?

[Voutloos]

Tja, dan heb je misschien wel nog een uniontje extra nodig.

Maar goed, trivia allemaal wel leuk en aardig, maar joins moet je gewoon vol uitschrijven inc. on clause. Het type join is een bewuste keuze, dus laat dat zien (ergo gebruik niet enkel de ',' operator), en laat dan ook meteen zien welke condities puur op het joinen slaan. Leesbaarder, relaties duidelijker en de bedoeling van de query in het algemeen duidelijker dan wanneer je alles op een grote hoop in de where clause flikkert.

[Confusion]

pedorus schreef op woensdag 04 juni 2008 @ 08:16:
Dus hoe vervang je

SQL:

select a.foo, b.bar from a left join b on a.baz = b.fuz

?

'Alleen een where clause' is inderdaad te kort door de bocht. Mijn eigenlijke punt was dat join statements syntactic sugar zijn; oorspronkelijk waren ze er helemaal niet. Verder gewoon gebruiken waar van toepassing; syntactic sugar wordt met goede reden toegevoegd natuurlijk.

Voor het leuke puzzelen: de vervanging van bovenstaande is (denk ik; niet getest)
[knip klopt niet]

[ Voor 48% gewijzigd door Confusion op 04-06-2008 10:19 ]

[The.Terminator]

Bozozo schreef op woensdag 04 juni 2008 @ 00:12:
Mierenneukmodus:


[...]


...heeft niet zo veel nut

Mag ik vragen waarom ?
En wat ik zeg, krijg nu wel alle 12 results, maar de niet gevalideerde (die dus hidden moeten zijn) worden ook geteld

[pedorus]

Vanwege mijn gemierenneuk over onduidelijkheden/onwaarheden in de FAQ enzo, zal ik voor de zekerheid het antwoord maar geven:

The.Terminator schreef op woensdag 04 juni 2008 @ 09:15:
Mag ik vragen waarom ?

Je vraagt om een kolom met alleen maar 1-tjes (adverts.isvisible).

En wat ik zeg, krijg nu wel alle 12 results, maar de niet gevalideerde (die dus hidden moeten zijn) worden ook geteld

Zie deze oplossing. Alternatief:

SQL:

CREATE VIEW activatedBiddings AS 
    SELECT * FROM biddings WHERE activationid IS NULL;
CREATE VIEW visibleAdverts AS 
    SELECT * FROM adverts WHERE isvisible='1';

En dan wordt de SQL iets als:

SQL:

SELECT a.id, a.title, a.views, a.date, COUNT(b.id) as bids 
FROM visibleAdverts a LEFT JOIN activatedBiddings b ON a.id=b.advertid 
WHERE a.categoryid='1' GROUP BY a.id ORDER BY a.date DESC

Zie verder documentatie over views (van Mysql). En het blijft eeuwig jammer dat "id" niet gewoon "advertId" heet.

[Verwijderd]

pedorus schreef op woensdag 04 juni 2008 @ 23:02:
En het blijft eeuwig jammer dat "id" niet gewoon "advertId" heet.

Want?

[DamadmOO]

pedorus schreef op woensdag 04 juni 2008 @ 08:16:
Dus hoe vervang je

SQL:

select a.foo, b.bar from a left join b on a.baz = b.fuz

?

SQL:

SELECT a.foo, b.bar 
FROM a, b 
WHERE a.baz *= b.fuz

Dit zou ik persoonlijk echter NOOIT gebruiken. Als je in bovenstaande situatie nog andere dingen in je where class zou willen zetten dan moeten deze over het algemeen naar de HAVING toe. Omdat je where expressies niet in een specifieke order behandelt worden.

*= is een left join
=* een right join
*=* een full outer join.

[ Voor 36% gewijzigd door DamadmOO op 05-06-2008 09:20 ]

[Remus]

pedorus schreef op woensdag 04 juni 2008 @ 23:02:
En het blijft eeuwig jammer dat "id" niet gewoon "advertId" heet.

Waarom? Het is nogal dubbelop om een record in de tabel advert ook nog eens een prefix te geven met die tabelnaam.

[LuCarD]

Remus schreef op donderdag 05 juni 2008 @ 14:23:
[...]


Waarom? Het is nogal dubbelop om een record in de tabel advert ook nog eens een prefix te geven met die tabelnaam.

Sommige mensen vinden dat een PK naam ( soms zelfs alle velden ) unique moet zijn in de gehele database. Dit maakt joins overzichtelijker, maar het is een persoonlijke mening. En hoeft zeker niet gevolgd te worden.

Wat ik persoonlijk belangrijker vind is om de Alias een duidelijke naam te geven.

SQL:

SELECT a.id, a.title, a.views, a.date, COUNT(b.id) as bids 
FROM visibleAdverts a LEFT JOIN activatedBiddings b ON a.id=b.advertid 
WHERE a.categoryid='1' GROUP BY a.id ORDER BY a.date DESC

Dit valt nog wel mee. Maar ik krijg soms queries over mijn scherm, waar a tot met l als alias is gedefineerd. En dan is het soms wel even puzzelen waar die vandaan komen.

[ Voor 34% gewijzigd door LuCarD op 05-06-2008 14:42 ]

[Verwijderd]

LuCarD schreef op donderdag 05 juni 2008 @ 14:38:
Dit valt nog wel mee. Maar ik krijg soms queries over mijn scherm, waar a tot met l als alias is gedefineerd. En dan is het soms wel even puzzelen waar die vandaan komen.

Helemaal mee eens. Om een of andere reden is het er vooral bij SQL ingeslopen om luiheid boven leesbaarheid te verkiezen. Zoals ik al vaker heb geroepen: "Het mooiste alias is de tabelnaam zelf". Je hebt immers bedacht dat 'user' een handige naam is voor een tabel met gebruikers, het is dan raar om dit later 'u' te noemen. Dat is simpelweg afbreuk aan informatie.

[LuCarD]

Verwijderd schreef op donderdag 05 juni 2008 @ 14:56:
[...]
Helemaal mee eens. Om een of andere reden is het er vooral bij SQL ingeslopen om luiheid boven leesbaarheid te verkiezen. Zoals ik al vaker heb geroepen: "Het mooiste alias is de tabelnaam zelf". Je hebt immers bedacht dat 'user' een handige naam is voor een tabel met gebruikers, het is dan raar om dit later 'u' te noemen. Dat is simpelweg afbreuk aan informatie.

Helaas gaat die vlieger ook niet altijd op. Als je verschillende joins naar de zelfde tabel maakt. bijvoorbeeld in ons geval. Een bepaald type transactie heeft een join naar klant ( verkoper ), klant ( koper ), klant ( financier ).

[Voutloos]

Dan gaat het juist ook op, aangezien je dan al gauw k1, k2 en k3 ziet, terwijl klant_koper, klant_verkoper en klant_financier (of slechts koper, verkoper en financier, met desnoods een hongaarse prefix k voor klant) duidelijker zijn.

offtopic:
Topic is al lang en breed opgelost en gaat nu van join alternatief trivia over op een naming style discussie.

[ Voor 21% gewijzigd door Voutloos op 05-06-2008 15:16 ]

[Confusion]

Voutloos schreef op donderdag 05 juni 2008 @ 15:14:

offtopic:
Topic is al lang en breed opgelost en gaat nu van join alternatief trivia over op een naming style discussie.

Tjah, hoe erg is dat eigenlijk? Voor de search is dit topic van weinig waarde; het is een te specifiek probleem met te generieke termen in de posts. De discussie die er uit opbloeit is misschien nog van meerwaarde voor de deelnamers, waardoor de netto meerwaarde van dit topic meer wordt.

En zo evolueert het naar een meta-discussie .