Ik moet in een listbox kunnen kiezen voor een auto (veld "AUTO" bevat het kenteken). Via een join wil ik uit een kilometerregistratie (tabel "uren")de laatste kilometerstand erbij weergeven. Een selectie van één auto gaat wel wanneer ik op kenteken opvraag, maar het lukt me nog niet om een lijstje te krijgen met alle auto's met daarbij per auto de laatste kilometerstand.
In de praktijk gaat het om bijvoorbeeld 10 auto's, waarbij er in de kilometerregistratie duizenden ritten staan.
<?php
$queryKM="SELECT Uren.AUTO, Uren.KM, Auto.merk FROM Uren INNER JOIN Auto ON Uren.AUTO = Auto.kenteken ORDER BY Uren.KM DESC LIMIT 1";
?>
Wanneer de query goed gaat is de verdere verwerking geen probleem meer.
$queryKM="SELECT Uren.AUTO, MAX(Uren.KM3), Auto.merk FROM Uren INNER JOIN Auto ON Uren.AUTO = Auto.kenteken GROUP BY Uren.auto, auto.merk";
// query toont 2 van de 10 kentekens van "auto", geen km's
Dit is het relevante stuk (jammer dat het het in stukjes wordt weergegeven):
<?php
$con = mysql_connect("","","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("my_db", $con);
$queryKM="SELECT Uren.AUTO, MAX(Uren.KM3), Auto.merk FROM Uren INNER JOIN Auto ON Uren.AUTO = Auto.kenteken GROUP BY Uren.auto, auto.merk";
$resultKM=mysql_query($queryKM,$con);
?>
als je om het hele stuk [.code.][/.code.] zet (zonder de puntjes) dan gaat het wel goed.
Maar ik heb het probleem al gevonden zoals ik dacht :-)
Je probeert de km stand met $getuserrow4['KM3'] weg te schrijven. Dat werkt alleen niet, omdat door de MAX() functie de kolom naam in MySQL nu "MAX(KM3)" wordt. Geef daar een alias mee en het werkt wel. Dus:
SELECT Uren.AUTO, MAX(Uren.KM3) AS KM3, Auto.merk
FROM Uren
INNER JOIN Auto ON Uren.AUTO = Auto.kenteken
GROUP BY Uren.auto, auto.merk
Yes, super! Ik zie nu kilometers achter het kenteken, waarvoor dank!
Maar ik zie nog steeds maar 2 van de 10 auto's van de auto tabel. M.a.w. auto's waarvan nog nooit kilometers geboekt zijn worden niet getoond. Deze zou dus wel getoond moeten worden, maar dan zonder kilometers (of met 0 kilometers).
Ja. Een INNER JOIN pakt alleen maar rijen die in beide tabellen bestaan. Een LEFT JOIN daarintegen pakt alle rijen in de linker tabel (de hoofdtabel zeg maar die in je FROM clause) staat en plakt daar rijen uit de andere tabel (in de JOIN) tegenaan, of geeft alle kolommen een NULL als er geen rij in de rechter tabel is.
Dus als je gewoon INNER JOIN vervangt door LEFT JOIN dan krijg je alle auto's te zien. Wat je dan ook nog kan doen is het maximum een mooie waarde geven door het volgende:
SELECT Uren.AUTO, COALESCE(MAX(Uren.KM3),'geen waarde') AS KM3, Auto.merk
FROM Uren
LEFT JOIN Auto ON Uren.AUTO = Auto.kenteken
GROUP BY Uren.auto, auto.merk
De functie COALESCE() pakt de eerste waarde die is meegegeven die niet NULL is. Als er dus een kilometerstand bekent is wordt die gegeven, anders de andere waarde (in dit geval 'geen waarde', maar dat kan je geven wat je wilt natuurlijk).
Je zal me ondertussen wel een zeur vinden, maar het gaat ondanks je hulp nog niet goed.
Ik krijg nu drie regels te zien (en geen 10), de 1e regel is gevuld met "geen waarde" en dan 2 regels met kentekens. Dit is logisch, want in mijn voorbeeld tabel uren zijn er 2 auto's gebruikt en zijn er een paar regels zonder kenteken.
Volgens mij moet de linker tabel de auto zijn, en moet deze gejoind worden met de uren. Of raak ik nu helemaal het spoor bijster?
[size=xsmall]Toevoeging op 03/02/2012 20:37:07:[/size]
$queryKM="SELECT Auto.kenteken, Auto.merk, COALESCE(MAX(Uren.KM3),'geen waarde') AS KM3 FROM Auto LEFT JOIN Uren ON Auto.kenteken = Uren.AUTO GROUP BY Auto.kenteken, auto.merk";
Nu gaat het wel goed!
[size=xsmall]Toevoeging op 03/02/2012 21:13:50:[/size]
Nog één kleine aanpassing nodig, ik heb van de table "auto" alleen de kentekens nodig waarvan het veld "actief" de waarde 1 heeft.
Ik heb al geprobeerd om "where auto.actief = 1" aan de query toe te voegen, maar dan geeft de query geen records weer.
$queryKM="SELECT Auto.kenteken, Auto.type, Auto.merk, COALESCE(MAX(Uren.KM3),'0') AS KM3 FROM Auto WHERE ACTIEF=1 LEFT JOIN Uren ON Auto.kenteken = Uren.AUTO GROUP BY Auto.kenteken, auto.merk";
Er zijn 5 auto's met voor het "ACTIEF" de waarde 1 (bij een andere query met listbox werkt de selectie wel).
Heb ik 't op de verkeerde plek in de query opgenomen?
Daarmee kom ik dus weer terug op mijn eerdere opmerking:
Erwin H op 03/02/2012 17:02:09
Post svp de code die je daadwerkelijk gebruikt.
Want ja, je statement klopt op deze manier niet.
Het zou moeten zijn:
SELECT Auto.kenteken, Auto.type, Auto.merk, COALESCE(MAX(Uren.KM3),'0') AS KM3
FROM Auto
LEFT JOIN Uren ON Auto.kenteken = Uren.AUTO
WHERE ACTIEF=1
GROUP BY Auto.kenteken, auto.merk
Een JOIN statement moet je eigenlijk zien als een onderdeel van je FROM statement. In dat blok (FROM en JOINs) bepaal je namelijk uit welke tabellen je data selecteert. Daarna pas ga je in een WHERE statement zeggen onder welke voorwaarden dat moet gebeuren.
