Join op kenteken en laatste kilometerstand nodig t.b.v. listbox query
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.
Code (php)
1
2
3
2
3
<?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";
?>
$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.
Groeten, Gerrit
Code (php)
1
2
3
4
5
2
3
4
5
SELECT Auto.auto, Uren.kilometerstand
FROM Auto
INNER JOIN Uren
ON Auto.P_Id=Uren.P_Id
ORDER BY Uren.kilometerstand
FROM Auto
INNER JOIN Uren
ON Auto.P_Id=Uren.P_Id
ORDER BY Uren.kilometerstand
Gewijzigd op 03/02/2012 16:15:51 door Reshad F
Je moet natuurlijk wel een onderscheid kunnen maken aan velden die gelinkt kunnen worden. Je selecteert bijvoorbeeld Auto.merk maar dan moet je ook auto.merk joinen op de uren.auto waar uren.auto dan ofwel het merk is of iets anders als id.
Reshadd farid op 03/02/2012 16:14:59:
even een vraagje heb je een primaire sleutel die in beide tables zit? zo ja dan kan je het zo doen:
Code (php)
1
2
3
4
5
2
3
4
5
SELECT Auto.auto, Uren.kilometerstand
FROM Auto
INNER JOIN Uren
ON Auto.P_Id=Uren.P_Id
ORDER BY Uren.kilometerstand
FROM Auto
INNER JOIN Uren
ON Auto.P_Id=Uren.P_Id
ORDER BY Uren.kilometerstand
Nee, de primary key 'moet' naar een foreign key verwijzen. Dus zou bijvoorbeeld Uren.auto_id kunnen zijn.
Code (php)
1
2
3
4
2
3
4
SELECT Uren.AUTO, MAX(Uren.KM), Auto.merk
FROM Uren
INNER JOIN Auto ON Uren.AUTO = Auto.kenteken
GROUP BY Uren.auto, auto.merk
FROM Uren
INNER JOIN Auto ON Uren.AUTO = Auto.kenteken
GROUP BY Uren.auto, auto.merk
Toevoeging op 03/02/2012 16:22:04:
Erwin H op 03/02/2012 16:19:28:
Als je er vanuit kan gaan dat de kilometerstand alleen maar oploopt en nooit afloopt (lijkt mij logisch, maar je weet nooit...) dan kan je met een MAX() functie alleen die uitlezen en met GROUP BY het splitsen per auto. Zoiets dus:
je doet nu INNER JOIN Auto ON uren.AUTO = Auto.kenteken
dus dan heb je de uren en een kenteken toch?
maar moeten die velden niet matchen?
Code (php)
1
2
3
4
2
3
4
SELECT Uren.AUTO, MAX(Uren.KM), Auto.merk
FROM Uren
INNER JOIN Auto ON Uren.AUTO = Auto.kenteken
GROUP BY Uren.auto, auto.merk
FROM Uren
INNER JOIN Auto ON Uren.AUTO = Auto.kenteken
GROUP BY Uren.auto, auto.merk
je doet nu INNER JOIN Auto ON uren.AUTO = Auto.kenteken
dus dan heb je de uren en een kenteken toch?
maar moeten die velden niet matchen?
Met de laatste code van Reshadd krijg ik de kentekens in beeld, zonder de kilometerstanden. Verder zijn de kentekens die nog niet in de kilometerregistratie staan vermeld NIET meer zichtbaar. Een nieuwe auto kun je die dus niet kiezen.
De tabel "auto" heeft "AUTOINCREMENT" als primary key (autoincrementing...); voor "uren" is dit eveneens het veld "AUTOINCREMENT". Deze zijn int(11). Het KM veld kan in sommige records ook null waarden bevatten of 0 zijn (urenregel zonder gebruik auto).
Join kan m.i. op uren.auto en auto.kenteken (bevat steeds het kenteken).
Gerrit broekhuis op 03/02/2012 16:43:02:
Met de laatste code van Reshadd krijg ik de kentekens in beeld, zonder de kilometerstanden.
Post svp de code die je daadwerkelijk gebruikt. Hier hebben we weinig aan, omdat de ervaring leert dat mensen nogal eens belangrijke punten vergeten of tikfouten maken bij het copieren. Als je de code post die jij gebruikt hebben we het altijd over hetzelfde.
$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
De tabel uren wordt als volgt gemaakt:
CREATE TABLE `uren` (
`TECHNICUS` char(4) DEFAULT NULL,
`WERKDATUM` date DEFAULT NULL,
`AANV_WERK` char(5) DEFAULT NULL,
`EINDE_WERK` char(5) DEFAULT NULL,
`WERKUUR` decimal(6,2) DEFAULT NULL,
`DECLA_1` tinyint(4) DEFAULT NULL,
`REISUUR` decimal(6,2) DEFAULT NULL,
`DECLA_2` tinyint(4) DEFAULT NULL,
`KM` int(11) DEFAULT NULL,
`DECLA_3` tinyint(4) DEFAULT NULL,
`UURSOORT` char(8) DEFAULT NULL,
`WERKBONNR` int(11) DEFAULT NULL,
`WERKPERIOD` int(11) DEFAULT NULL,
`WERKJAAR` int(11) DEFAULT NULL,
`AANV_REIS` char(5) DEFAULT NULL,
`EINDE_REIS` char(5) DEFAULT NULL,
`PDA_ID` char(40) DEFAULT NULL,
`LEN_UITV` int(11) DEFAULT NULL,
`LEN_MAT` int(11) DEFAULT NULL,
`RANDOMNR` bigint(11) DEFAULT NULL,
`OPLOSSING` text,
`MATERIAAL` text,
`KM1` int(11) DEFAULT NULL,
`KM2` int(11) DEFAULT NULL,
`KM3` int(11) DEFAULT NULL,
`AUTO` char(10) DEFAULT NULL,
`STRIPNR` int(11) DEFAULT NULL,
`AUTOINCREMENT` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`AUTOINCREMENT`)
De tabel auto is als volgt opgebouwd:
CREATE TABLE `auto` (
`kenteken` char(8) DEFAULT NULL,
`merk` char(20) DEFAULT NULL,
`type` char(20) DEFAULT NULL,
`apkdatum` date DEFAULT NULL,
`beheer` char(5) DEFAULT NULL,
`ACTIEF` tinyint(4) DEFAULT NULL,
`AUTOINCREMENT` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`AUTOINCREMENT`)
)
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
<?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);
?>
$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);
?>
<table border="1"><tr><td>Auto met KM-stand: </td><td>
<select name="id_auto">
<option value="">Kies een auto!</option>
<option value=""></option>
</select></td></tr></table>
Gewijzigd op 03/02/2012 19:24:44 door gerrit broekhuis
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:
Code (php)
1
2
3
4
2
3
4
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
FROM Uren
INNER JOIN Auto ON Uren.AUTO = Auto.kenteken
GROUP BY Uren.auto, auto.merk
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).
Kan ik die auto's ook zichtbaar maken?
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:
Code (php)
1
2
3
4
2
3
4
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
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).
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?
Toevoeging op 03/02/2012 20:37:07:
$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!
Toevoeging op 03/02/2012 21:13:50:
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.
Gerrit broekhuis op 03/02/2012 20:31:11:
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?
Klinkt inderdaad wel logisch en dat was dus ook de oplossing :-)
Gerrit broekhuis op 03/02/2012 20:31:11:
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.
Ik heb al geprobeerd om "where auto.actief = 1" aan de query toe te voegen, maar dan geeft de query geen records weer.
Lijkt me dat dat wel zou moeten werken. Dus enige domme vraag die ik kan stellen is.... zitten er nu dan wel auto's in de database met actief = 1?
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?
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:
Code (php)
1
2
3
4
5
2
3
4
5
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
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.
Gewijzigd op 04/02/2012 15:41:51 door Erwin H