Left JOIN query
Ik wil iig alle klanten op deze pagina, dus ik dacht Left Join
maar dan wil/ hoef ik alleen de laatste factuur van die klant te zien. Klanten die nog geen factuur hebben moeten echter ook gewoon in de lijst staan.
SELECT k.id, k.name, k.adres, k.tel, f.amount, f.paid, f.datum
FROM klanten as k
LEFT JOIN facturen as f
ON k.id=f.user_id
geeft alle klanten met al hun facturen
Om van elke klant alleen laatste factuur te zien dacht ik aan GROUP klant
dat werkt, maar ik krijg dan de oudste factuur. De order by f.datum werkt niet!
Heeft iemand een oplossing ? Of kan dit niet in 1 query ?
SELECT k.id, k.name, k.adres, k.tel, f.amount, f.paid, f.datum
FROM klanten as k
LEFT JOIN facturen as f
ON k.id=f.user_id
GROUP BY k.id
ORDER BY f.datum ASC
Gewijzigd op 20/01/2012 14:05:42 door - Roland -
Gewijzigd op 20/01/2012 14:00:36 door Erwin H
Aangepast: k (klanten)
Haal de facturen op, op basis van de klant_id via een losse query. Beste oplossing.
Een GROUP BY zonder een group function (MAX(), MIN(), SUM(), COUNT() etc) werkt niet. Althans, niet goed. Als je dus alleen de nieuwste zou willen selecteren, zal je met een MAX(f.datum) moeten werken. Je kan dus dit proberen, maar ik weet niet zeker of het gaat werken:
Code (php)
1
2
3
4
5
2
3
4
5
SELECT k.id, k.name, k.adres, k.tel, f.amount, f.paid, MAX(f.datum)
FROM klanten as k
LEFT JOIN facturen as f
ON k.id=f.user_id
GROUP BY k.id
FROM klanten as k
LEFT JOIN facturen as f
ON k.id=f.user_id
GROUP BY k.id
Ik vraag me namelijk af of dan nog wel de juiste facturen worden geselecteerd. Ik vrees eerlijk gezegd van niet.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT
k.id,
k.naam,
k.adres,
f.amount,
f.paid
FROM
klanten as k,
(
SELECT
amount,
paid,
user_id
FROM
factuur
ORDER BY datum DESC
) as f
WHERE
f.user_id = k.id
GROUP BY k.id
k.id,
k.naam,
k.adres,
f.amount,
f.paid
FROM
klanten as k,
(
SELECT
amount,
paid,
user_id
FROM
factuur
ORDER BY datum DESC
) as f
WHERE
f.user_id = k.id
GROUP BY k.id
Gewijzigd op 20/01/2012 14:20:31 door Lendl Verschoor
@ Erik GROUP_CONCAT kan zeker sosm interessant zijn (nog nooit gebruikt !)
Volgens mij gaat mysql heel slecht om met een subquery
Dat hij die query elke keer weer afvoert per rij in klanten.
Dus op een grote tabel is hij rete lang bezig
Ja, die is ook goed, althans hij geeft het goed maar klanten zonder factuur komen er natuurlijk NIET in voor, toch bedankt !
- Roland - op 20/01/2012 13:54:20:
Om van elke klant alleen laatste factuur te zien dacht ik aan GROUP klant
dat werkt, maar ik krijg dan de oudste factuur. De order by f.datum werkt niet!
SELECT k.id, k.name, k.adres, k.tel, f.amount, f.paid, f.datum
FROM klanten as k
LEFT JOIN facturen as f
ON k.id=f.user_id
GROUP BY k.id
ORDER BY f.datum ASC
dat werkt, maar ik krijg dan de oudste factuur. De order by f.datum werkt niet!
SELECT k.id, k.name, k.adres, k.tel, f.amount, f.paid, f.datum
FROM klanten as k
LEFT JOIN facturen as f
ON k.id=f.user_id
GROUP BY k.id
ORDER BY f.datum ASC
Er staat ook ASC in de ORDER moet dat geen DESC zijn.
Een andere oplossing is om een index op de factuur tabel te zetten voor de datum kolom
Erik Rijk op 20/01/2012 14:24:12:
Nogal overkill voor zoiets simpels eerlijk gezegd.
Heeft daar iemand documentatie over?
Of werken met subquery's veel vertraagt?
Want het ding is wel, vaak kan je (heel) lastige query's gemakkelijk oplossen op deze manier.
Dingen zoals:
In een voetbal-klassement random drie ploegen uit de top 10 tonen
(waarschijnlijk zijn er betere voorbeelden)
Gewijzigd op 20/01/2012 17:38:10 door Kris Peeters
Als dat niet het geval is dan zit je naar een resultaat te kijken wat in 99% van de gevallen niet zal en kan kloppen.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
SELECT k.id,
k.name,
k.adres,
k.tel,
f.amount,
f.paid,
f.datum
FROM klanten k,
facturen f
WHERE k.id=f.user_id
AND f.datum = (SELECT max(x.datum)
FROM facturen x
WHERE x.id=f.user_id)
k.name,
k.adres,
k.tel,
f.amount,
f.paid,
f.datum
FROM klanten k,
facturen f
WHERE k.id=f.user_id
AND f.datum = (SELECT max(x.datum)
FROM facturen x
WHERE x.id=f.user_id)
Gewijzigd op 20/01/2012 22:09:02 door Aad B
Noppes Homeland op 20/01/2012 18:52:36:
Alle queries die hier geschreven staan met een GROUP BY dienen een foutmelding op te leveren!!!!
Als dat niet het geval is dan zit je naar een resultaat te kijken wat in 99% van de gevallen niet zal en kan kloppen.
Als dat niet het geval is dan zit je naar een resultaat te kijken wat in 99% van de gevallen niet zal en kan kloppen.
Noppes, doen iedereen hier eens een plezier en leg je statements uit. Dat "alle voorbeelden een foutmelding op moeten leveren" heeft natuurlijk niemand iets aan. Waarom dat het geval zou moeten zijn en wat dan beter kan wel. Ook ik wil graag leren, maar van jouw antwoorden heb ik nog nooit iets geleerd...
Kom, doe eens moeite, doe ons eens een plezier en leg je woorden eens uit. Dan verdien je ook mijn respect.
k.id,
k.naam,
MAX(f.factuurnr),
MAX(f.factuurdatum)
FROM
klanten k
INNER JOIN facturen f ON k.id = f.user_id
GROUP BY f.user_id
Denk dat deze query de juiste resultaten geeft.
Code (php)
1
2
3
2
3
datum factuurnr user_id
20-10-2011 APO-123456789 1
20-02-2011 ZIP-123456789 1
20-10-2011 APO-123456789 1
20-02-2011 ZIP-123456789 1
dan krijg jij toch mooi een combi van 20-10-2011 / ZIP-123456789
@Noppes, waarom zou een GROUP BY een foutmelding weergeven? Ik ben daar toch wel heeeeeeeeeeel nieuwsgierig naar!.
Aad B op 20/01/2012 22:00:05: Om performance te garanderen moet er wel een index staan op user_id als dit geen primairy key is. Er wordt dan een index-range scan uitgevoerd om de max datum te kunnen vinden. Dat moet een bruikbare performance opleveren tenzij je per klant duizenden facturen hebt. In dat geval moet je een andere oplossing zoeken.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
SELECT k.id,
k.name,
k.adres,
k.tel,
f.amount,
f.paid,
f.datum
FROM klanten k,
facturen f
WHERE k.id=f.user_id
AND f.datum = (SELECT max(x.datum)
FROM facturen x
WHERE x.id=f.user_id)
k.name,
k.adres,
k.tel,
f.amount,
f.paid,
f.datum
FROM klanten k,
facturen f
WHERE k.id=f.user_id
AND f.datum = (SELECT max(x.datum)
FROM facturen x
WHERE x.id=f.user_id)
En eventueel nog een index op f.datum. Eigenlijk een index op f.user_id en f.datum, dat is het beste.
Subqueries hoeven niet langzaam te zijn, ik gebruik ze ook nog weleens. Maar je moet er wel mee oppassen en vaak kan het ook met een join oid.
Ger van Steenderen op 21/01/2012 18:13:30:
@Noppes, waarom zou een GROUP BY een foutmelding weergeven? Ik ben daar toch wel heeeeeeeeeeel nieuwsgierig naar!.
Ik vermoed omdat je 'officieel' alle scalar values moet grouperen als je group by gebruikt. Alleen MySQL pikt dit ook.
Gewijzigd op 21/01/2012 18:20:43 door kees Schepers
Mijn aanname dat het hoogste factuurnummer ook bij de hoogste datum hoort heeft er mee te maken dat dit van de belastingdienst moet.
Ben trouwens erg benieuwd naar wat voor fout mijn query moet leiden
Als je ook de klanten die geen factuur hebben gekregen kun je dus gewoon er een left join van maken:
SELECT k.id,
k.name,
k.adres,
k.tel,
f.amount,
f.paid,
MAX(f.factuurnr),
MAX(f.datum)
FROM
klanten k
LEFT JOIN facturen f ON k.id = f.user_id
GROUP BY f.user_id
Gewijzigd op 21/01/2012 19:06:54 door Gerben G