MYSQL JOIN's

[watercoolertje]

Na ongeveer 12 jaar (php/mysql) ervaring zou ik toch denken dat een simpele JOIN zo gemaakt is gek genoeg krijg ik niet de resultaten die ik hoopte en loop ik er behoorlijk op vast...

Uiteraard heb ik op internet nog gezocht hoe het allemaal precies werkt (ookal heb ik er al aardig wat ervaring mee ) en verschillende sites afgezocht naar een goede oplossing maar tot op heden niet gevonden!

Ik heb de volgende QUERY:

code:

SELECT * , SUM( hits ) AS hits
FROM `counter_date` AS cd
LEFT JOIN `counter_visits_extra` AS cve ON date = dates
LEFT JOIN `counter_visits` AS cv ON cv.visitid = cve.visitid
WHERE keyword = 'test'
GROUP BY dates
ORDER BY dates DESC
LIMIT 14

In de tabel counter date staat ELKE dag (in een date-field) die er is, zodat als er voor een dag geen stats zijn die dag toch getoond wordt, ten minste dat is het idee want het gebeurt niet

Eigenlijk heb ik elke mogelijk type JOIN al uitgeprobeerd (zo hopeloos ben/was ik) LEFT en RIGHT, INNER en OUTER, zelfs de normale JOIN, maar ze geven allemaal exact hetzelfde terug...

Wat ik dus heel simpel wil is als er in de tabel counter_visits_extra of counter_visits geen data van een dag bekend is dat ik toch die dag terug zie in de resultaten, dus:
05-08-2010 (1 hits)
04-08-2010 (0 hits)
03-08-2010 (3 hits)
02-08-2010 (1 hits)
01-08-2010 (0 hits)

Maar ik krijg de dagen met 0 hits niet terug (oftewel maar 3 records ipv 5)... Iemand enig idee, want hier vallen me haren bijna van uit me kop, aangezien ik alles wat ik dacht te weten heb toegepast...

[RobIII]

Als je 12 jaar ervaring hebt zou je toch niet meer hoeven opzoeken hoe joins werken, hopeloos of niet. Maar here you go: Hoe werken joins? Wat ik raar vind is dat, volgens jou, alle type joins dezelfde resultaten geven. Maar je probleem lijkt me niet eens in je joins te zitten: Je hebt een WHERE clause; die al eens achterwege gelaten? Je beseft dat je daar ook records mee uitsluit? Ik vermoed dat de where clause in je post niet overeen komt met de werkelijkheid?

Ik vind ook de "...on date = dates" maar raar; ik zou daar een "on cd.x = cve.y" verwachten.

Verder: Hoe werkt dat GROUP BY nu eigenlijk? want je Group By klopt niet i.c.m. Select *

[ Voor 42% gewijzigd door RobIII op 05-08-2010 12:24 ]

[watercoolertje]

Naja 12 jaar wil niet zeggen 12 jaar pro ervaring ofso ik was op me 13de begonnen met PHP... Later is MYSQL daar bij gekomen, en ik werk nu 3 jaar in de branche (wel beetje houtje touwtje want alles moet snel snel, en dat gaat niet samen met goed goed )

En wat die namen betreft, dat doe ik enkel bij unieke velden niet, de structuur is zo opgezet dat date en/of dates in geen 1 andere tabel staat,... Kwa netheid niet geweldig maar dat zou toch echt moeten werken Had daar inderdaad wel een opmerking over zien aankomen (maar om het nou voor tweakers goed neer te zetten hou ik mezelf en jullie alleen maar voor de gek).

Die van de GROUP BY vat ik eigenlijk niet, die werkt werkelijk PERFECT zolang er geen join in het spel is (gebruik hem heel vaak zo, tenzij ik niet alle data uit de tabel(len) nodig heb) en nooit problemen mee gehad.

Aangezien ik nu midden in de lunch zit ga ik zo de aangehaalde artikelen nog is goed bestuderen (uiteraard al eerder gedaan)...

En the WHERE die kan ik niet weg laten, ik krijg anders namelijk ELKE dag sowieso terug omdat er dagelijks tot een miljoen hits bij komen Ik wil per keywordt dus de hits uitzoeken

[ Voor 8% gewijzigd door watercoolertje op 05-08-2010 12:42 ]

[RobIII]

watercoolertje schreef op donderdag 05 augustus 2010 @ 12:36:
En wat die namen betreft, dat doe ik enkel bij unieke velden niet

Ik zou, persoonlijk, gewoon aanwennen gestructureerd te werken. Dus consequent die aliases gebruiken.

watercoolertje schreef op donderdag 05 augustus 2010 @ 12:36:
de structuur is zo opgezet dat date en/of dates in geen 1 andere tabel staat

Tot er een keer komt dat iemand ergens wel zo'n veld bij klust, dan kun je alle queries gaan nalopen. Verder: ik ben nooit zo kapot van (potentieel!) reserved words als "date". Gebruik liever "invoice_date" oid. of escape ze: select `abc`, `def`, `date`, ....

watercoolertje schreef op donderdag 05 augustus 2010 @ 12:36:
Die van de GROUP BY vat ik eigenlijk niet, die werkt werkelijk PERFECT zolang er geen join in het spel is (gebruik hem heel vaak zo, tenzij ik niet alle data uit de tabel(len) nodig heb) en nooit problemen mee gehad.

In een GROUP BY hoor je alle velden expliciet te vermelden die niet in aggregates zijn opgenomen. Dat MySQL dit toestaat is andere koek. Uit de FAQ waar ik eerder naar verwees:

quote: http://gathering.tweakers..._messages/1105378#groupby

MySQL is een hele brakke database, die deze laatste constructie wel toestaat. En volgens de handleiding is het 'by design' dat je vervolgens random waardes in kolom B aantreft. Don't do it.

Het zal best in bep. gevallen (al dan niet 'toevallig') goed gaan / werken maar ik raad je aan te zorgen dat je expliciet bent in dit soort dingen.

watercoolertje schreef op donderdag 05 augustus 2010 @ 12:36:
En the WHERE die kan ik niet weg laten, ik krijg anders namelijk ELKE dag sowieso terug omdat er dagelijks tot een miljoen hits bij komen Ik wil per keywordt dus de hits uitzoeken

Denk nou eens na Als je op een bepaalde dag geen hits hebt op dat keyword... dan gooit je WHERE clause dat record dus uit je resultset Het zit 'm dus niet in je joins zoals ik al zei maar in je WHERE clause. Je zult de voorwaarden uit je WHERE dus moeten verhuizen naar de join zelf (dus ...on x.y = a.b and keyword = 'test').

[ Voor 32% gewijzigd door RobIII op 05-08-2010 13:07 ]

[Redshark]

watercoolertje schreef op donderdag 05 augustus 2010 @ 12:36:
En wat die namen betreft, dat doe ik enkel bij unieke velden niet, de structuur is zo opgezet dat date en/of dates in geen 1 andere tabel staat,... Kwa netheid niet geweldig maar dat zou toch echt moeten werken Had daar inderdaad wel een opmerking over zien aankomen (maar om het nou voor tweakers goed neer te zetten hou ik mezelf en jullie alleen maar voor de gek).

En wij kunnen nu niet zien uit welke tabel welke kolomnaam komt :-)

Die van de GROUP BY vat ik eigenlijk niet, die werkt werkelijk PERFECT zolang er geen join in het spel is (gebruik hem heel vaak zo, tenzij ik niet alle data uit de tabel(len) nodig heb) en nooit problemen mee gehad.

Als je niet alle data uit je tabellen nodig hebt dan moet je die gewoon niet selecteren. Met * doe je dat wel. MySQL geeft geen fout op jouw query, Oracle en MS SQL wel

[watercoolertje]

En wij kunnen nu niet zien uit welke tabel welke kolomnaam komt :-)

Dat is opzich wel een goed argument

Bij die 2de opmerking ik heb nu dus wel alle data nodig, ik gaf aan als ik niet alle data nodig heb dat ik individuele verlden ga aanspreken ipv een *.

code:

SELECT * , SUM( hits ) AS hits
FROM `counter_date` AS cd
LEFT JOIN `counter_visits_extra` AS cve ON cve.date = cd.dates
LEFT JOIN `counter_visits` AS cv ON cv.visitid = cve.visitid
WHERE cv.keyword = 'test'
GROUP BY cd.dates
ORDER BY cd.dates DESC
LIMIT 14

@Rob11: ja als ik tijd krijg om er de tijd voor te nemen zal ik het echt wel netjes willen hoor, maarja die tijd heb ik hier gewoon, als het later daardoor niet werkt (omdat er velden toegevoegd worden door een ander oid) voel ik mij daar ook echt niet verantwoordelijk voor, diegene die mij te grote projecten in te weinig tijd probeerd te laten doen DIE is daarvoor de verantwoordelijke IMHO..

EDIT:
Anyway van die WHERE heb je gelijk, die WHERE moet ik dus bij de juiste JOIN plakken, krijg nu wel de goede data terug

code:

SELECT *, SUM( hits ) AS hits FROM `counter_visits` AS cv 
LEFT JOIN `counter_visits_extra` AS cve ON cve.visitid = cv.visitid AND cv.keyword = 'test' 
RIGHT JOIN `counter_date` AS cd ON cve.`date` = cd.dates 
GROUP BY dates ORDER BY dates DESC LIMIT 14

Echter moet ik van NULL nog 0 maken (aangezien de tabel nu lege plekken krijgt ipv een 0)

Bedankt voor de andere tips over me stijl, zoals ik al zei ik doe echt wel me best als ik de tijd er voor heb, krijg ik dat niet tja dan hebben ze er maar mee te leven!

[ Voor 25% gewijzigd door watercoolertje op 05-08-2010 13:20 ]

[cariolive23]

watercoolertje schreef op donderdag 05 augustus 2010 @ 12:36:
Die van de GROUP BY vat ik eigenlijk niet, die werkt werkelijk PERFECT zolang er geen join in het spel is (gebruik hem heel vaak zo, tenzij ik niet alle data uit de tabel(len) nodig heb) en nooit problemen mee gehad.

Dan heb je ook nog nooit met echte databases gewerkt en ook nog nooit goed en volledig getest. Normale databases geven hier direct een foutmelding op, die gaan niet gokken welke data jij vandaag zou willen zien.

De GROUP BY is compleet fout, gooi deze er in zijn geheel uit of ga alle kolommen opnemen die niet in een aggregate functie staan.

[Redshark]

watercoolertje schreef op donderdag 05 augustus 2010 @ 13:16:
[...]
Bij die 2de opmerking ik heb nu dus wel alle data nodig, ik gaf aan als ik niet alle data nodig heb dat ik individuele verlden ga aanspreken ipv een *.

Schrijf ze gewoon uit, scheelt je heus niet zoveel tijd hoor. (Bovendien post je hier nu ook...) Daarnaast kun je direct je group by eens goed bekijken. Alle kolommen waar je geen aggregratie-functie op gebruikt zijn ineens overzichtelijk en kun je zo in je group by plakken. Daar staat nu ook 1 veld ipv alles wat je selecteerd. Eigenlijk moet je hiervan:

code:

SELECT * , SUM( hits ) AS hits
FROM `counter_date` AS cd
LEFT JOIN `counter_visits_extra` AS cve ON cve.date = cd.dates
LEFT JOIN `counter_visits` AS cv ON cv.visitid = cve.visitid
WHERE cv.keyword = 'test'
GROUP BY cd.dates
ORDER BY cd.dates DESC
LIMIT 14

Dit maken

code:

SELECT 
    cd.dates
    , SUM(hits) AS hits
FROM 
    counter_date AS cd
LEFT JOIN 
    counter_visits_extra AS cve ON cve.date = cd.dates
LEFT JOIN 
    counter_visits AS cv ON cv.visitid = cve.visitid
    and cv.keyword = 'test'
GROUP BY cd.dates
ORDER BY cd.dates DESC
LIMIT 14

EDIT:
Echter moet ik van NULL nog 0 maken (aangezien de tabel nu lege plekken krijgt ipv een 0)

Daar is gewoon een functie voor (ook in MySQL gok ik)

Bedankt voor de andere tips over me stijl, zoals ik al zei ik doe echt wel me best als ik de tijd er voor heb, krijg ik dat niet tja dan hebben ze er maar mee te leven!

Als je het jezelf gewoon aanleert is het echt niet meer werk. Zal je in de toekomst zelfs tijd besparen, zeker wanneer je na een jaar weer in je code duikt.

[cariolive23]

Redshark schreef op donderdag 05 augustus 2010 @ 13:52:
[...]

Daar is gewoon een functie voor (ook in MySQL gok ik)

[...]

COALESCE(veldnaam, '0')

http://dev.mysql.com/doc/...rs.html#function_coalesce