group_concat maar lege velden niet in de group_concat opnemen
Met een query en dmv GROUP_CONCAT krijg ik als resultaat in een rij: values/de id's|
Onder een voorbeeld:
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
KOLOM
tsw1/19|
tsw2/20|
tsw3/21|
/22|
/23|
/24|
/25|
/26...
tsw1/19|
tsw2/20|
tsw3/21|
/22|
/23|
/24|
/25|
/26...
Nu zit er lege waardes bij (vanaf 22) en die moet niet opgenomen worden in het betreffende kolom
Dit is de lijn waar ik nu op concentreer:
(GROUP_CONCAT(CONCAT(cvt.cvt_TS_wat, '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_TS_wat,
Query zelf:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
bcv.bcv_id, bcv.bcv_laatste_functie, bcv.bcv_beschrijving,
GROUP_CONCAT(CONCAT(cvt.cvt_TS_wat, '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_TS_wat,
GROUP_CONCAT(DISTINCT CONCAT(cvt.cvt_TS_hoelang, '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_TS_hoelang,
GROUP_CONCAT(DISTINCT CONCAT(cvt.cvt_technisch_know_how , '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_technisch_know_how ,
cvs.cvs_S_wat, cvs.cvs_S_hoelang, cvs.cvs_scholing, cvw.cvw_WE_wat, cvw.cvw_WE_hoelang, cvw.cvw_WE_inzet, cvw.cvw_WE_bedrijf, cvw.cvw_werkervaring, ba_id, bcv.bcv_datum, bcv.bcv_foto FROM beheer_cv bcv
INNER JOIN cv_techskills cvt
ON cvt.bcv_id = bcv.bcv_id
INNER JOIN cv_scholing cvs
ON
cvs.bcv_id = bcv.bcv_id
INNER JOIN cv_werkervaring cvw
ON
cvw.bcv_id = bcv.bcv_id
WHERE ba_id= 5
bcv.bcv_id, bcv.bcv_laatste_functie, bcv.bcv_beschrijving,
GROUP_CONCAT(CONCAT(cvt.cvt_TS_wat, '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_TS_wat,
GROUP_CONCAT(DISTINCT CONCAT(cvt.cvt_TS_hoelang, '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_TS_hoelang,
GROUP_CONCAT(DISTINCT CONCAT(cvt.cvt_technisch_know_how , '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_technisch_know_how ,
cvs.cvs_S_wat, cvs.cvs_S_hoelang, cvs.cvs_scholing, cvw.cvw_WE_wat, cvw.cvw_WE_hoelang, cvw.cvw_WE_inzet, cvw.cvw_WE_bedrijf, cvw.cvw_werkervaring, ba_id, bcv.bcv_datum, bcv.bcv_foto FROM beheer_cv bcv
INNER JOIN cv_techskills cvt
ON cvt.bcv_id = bcv.bcv_id
INNER JOIN cv_scholing cvs
ON
cvs.bcv_id = bcv.bcv_id
INNER JOIN cv_werkervaring cvw
ON
cvw.bcv_id = bcv.bcv_id
WHERE ba_id= 5
-> Is er een filter binnen in de GROUP_CONCAT die de lege waardes niet opneemt en ipv de voorbeeld boven de kolom er zo uit laat komen te zien?
Code (php)
1
2
3
4
2
3
4
KOLOM
tsw1/19|
tsw2/20|
tsw3/21|
tsw1/19|
tsw2/20|
tsw3/21|
Gewijzigd op 03/02/2014 16:13:30 door Francoi gckx
Code (php)
1
2
2
.......
WHERE ba_id = 5 AND cvt.cvt_TS_hoelang IS NOT NULL
WHERE ba_id = 5 AND cvt.cvt_TS_hoelang IS NOT NULL
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
bcv.bcv_id, bcv.bcv_laatste_functie, bcv.bcv_beschrijving,
GROUP_CONCAT(CONCAT(cvt.cvt_TS_wat, '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_TS_wat,
GROUP_CONCAT(DISTINCT CONCAT(cvt.cvt_TS_hoelang, '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_TS_hoelang,
GROUP_CONCAT(DISTINCT CONCAT(cvt.cvt_technisch_know_how , '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_technisch_know_how ,
cvs.cvs_S_wat, cvs.cvs_S_hoelang, cvs.cvs_scholing, cvw.cvw_WE_wat, cvw.cvw_WE_hoelang, cvw.cvw_WE_inzet, cvw.cvw_WE_bedrijf, cvw.cvw_werkervaring, ba_id, bcv.bcv_datum, bcv.bcv_foto FROM beheer_cv bcv
INNER JOIN cv_techskills cvt
ON cvt.bcv_id = bcv.bcv_id
INNER JOIN cv_scholing cvs
ON
cvs.bcv_id = bcv.bcv_id
INNER JOIN cv_werkervaring cvw
ON
cvw.bcv_id = bcv.bcv_id
WHERE ba_id= 5
AND cvt.cvt_TS_wat IS NOT NULL
GROUP BY cvt.cvt_TS_wat
bcv.bcv_id, bcv.bcv_laatste_functie, bcv.bcv_beschrijving,
GROUP_CONCAT(CONCAT(cvt.cvt_TS_wat, '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_TS_wat,
GROUP_CONCAT(DISTINCT CONCAT(cvt.cvt_TS_hoelang, '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_TS_hoelang,
GROUP_CONCAT(DISTINCT CONCAT(cvt.cvt_technisch_know_how , '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_technisch_know_how ,
cvs.cvs_S_wat, cvs.cvs_S_hoelang, cvs.cvs_scholing, cvw.cvw_WE_wat, cvw.cvw_WE_hoelang, cvw.cvw_WE_inzet, cvw.cvw_WE_bedrijf, cvw.cvw_werkervaring, ba_id, bcv.bcv_datum, bcv.bcv_foto FROM beheer_cv bcv
INNER JOIN cv_techskills cvt
ON cvt.bcv_id = bcv.bcv_id
INNER JOIN cv_scholing cvs
ON
cvs.bcv_id = bcv.bcv_id
INNER JOIN cv_werkervaring cvw
ON
cvw.bcv_id = bcv.bcv_id
WHERE ba_id= 5
AND cvt.cvt_TS_wat IS NOT NULL
GROUP BY cvt.cvt_TS_wat
Ik krijg dit, in kolom cvt_TS_wat:
rij1 = /22| /23| /24| /25| /26| /27
rij2 = tsw1/19
rij3 = tsw2/20
rij4 = tsw3/21
En als ik de query zo uitvoer:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
bcv.bcv_id, bcv.bcv_laatste_functie, bcv.bcv_beschrijving,
GROUP_CONCAT(CONCAT(if(cvt.cvt_TS_wat = '', null, cvt.cvt_TS_wat ), '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_TS_wat,
GROUP_CONCAT(DISTINCT CONCAT(cvt.cvt_TS_hoelang, '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_TS_hoelang,
GROUP_CONCAT(DISTINCT CONCAT(cvt.cvt_technisch_know_how , '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_technisch_know_how ,
cvs.cvs_S_wat, cvs.cvs_S_hoelang, cvs.cvs_scholing, cvw.cvw_WE_wat, cvw.cvw_WE_hoelang, cvw.cvw_WE_inzet, cvw.cvw_WE_bedrijf, cvw.cvw_werkervaring, ba_id, bcv.bcv_datum, bcv.bcv_foto FROM beheer_cv bcv
INNER JOIN cv_techskills cvt
ON cvt.bcv_id = bcv.bcv_id
INNER JOIN cv_scholing cvs
ON
cvs.bcv_id = bcv.bcv_id
INNER JOIN cv_werkervaring cvw
ON
cvw.bcv_id = bcv.bcv_id
WHERE ba_id= ? AND cvt.cvt_TS_wat IS NOT NULL
GROUP BY cvt.cvt_TS_wat
bcv.bcv_id, bcv.bcv_laatste_functie, bcv.bcv_beschrijving,
GROUP_CONCAT(CONCAT(if(cvt.cvt_TS_wat = '', null, cvt.cvt_TS_wat ), '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_TS_wat,
GROUP_CONCAT(DISTINCT CONCAT(cvt.cvt_TS_hoelang, '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_TS_hoelang,
GROUP_CONCAT(DISTINCT CONCAT(cvt.cvt_technisch_know_how , '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_technisch_know_how ,
cvs.cvs_S_wat, cvs.cvs_S_hoelang, cvs.cvs_scholing, cvw.cvw_WE_wat, cvw.cvw_WE_hoelang, cvw.cvw_WE_inzet, cvw.cvw_WE_bedrijf, cvw.cvw_werkervaring, ba_id, bcv.bcv_datum, bcv.bcv_foto FROM beheer_cv bcv
INNER JOIN cv_techskills cvt
ON cvt.bcv_id = bcv.bcv_id
INNER JOIN cv_scholing cvs
ON
cvs.bcv_id = bcv.bcv_id
INNER JOIN cv_werkervaring cvw
ON
cvw.bcv_id = bcv.bcv_id
WHERE ba_id= ? AND cvt.cvt_TS_wat IS NOT NULL
GROUP BY cvt.cvt_TS_wat
GROUP_CONCAT(CONCAT(if(cvt.cvt_TS_wat = '', null, cvt.cvt_TS_wat ), '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_TS_wat
in kolom cvt_TS_wat:
rij1 = NULL
rij2 = tsw1/19
rij3 = tsw2/20
rij4 = tsw3/21
Daarnaast klopt je group_by niet, daar horen alle niet aggegrate kolommen uit de select list te staan, en jij zet er juist één in die er niet in voorkomt.
Dit zou eigenlijk gewoon een foutmelding moeten geven, bij de meeste databse systemen krijg je die ook, maar in mySQL niet (tenzij je FULL_GROUP_BY instelt op 1)
Gewijzigd op 03/02/2014 19:28:36 door Ger van Steenderen
De totale tabel ziet er zo uit:
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
cvt_id bcv_id cvt_technisch_know_how cvt_TS_wat cvt_TS_hoelang
19 12 tsw1
20 12 tsw2
21 12 tsw3
22 12 tsw1
23 12 tsw2
24 12 tsw3
25 12 kb1
26 12 kb2
27 12 kb3
19 12 tsw1
20 12 tsw2
21 12 tsw3
22 12 tsw1
23 12 tsw2
24 12 tsw3
25 12 kb1
26 12 kb2
27 12 kb3
GROUP BY cvt.cvt_TS_wat moet zeker GROUP BY cvt_TS_wat zijn ?
Toevoeging op 03/02/2014 19:33:15:
Ik hoop dat iemand het duidelijk kan zien maar is er geen beter manier om een tabel te tonen op phphulp en dan niet met printscreen?
Toevoeging op 03/02/2014 19:38:46:
O sorry Ger niet goed gelezen ik dacht juist wel alle aggegrate kolommen en dan bedoel je zeker bcv.bcv_id, bcv.bcv_laatste_functie, bcv.bcv_beschrijving, enzo even zien of dat het probleem zelf oplost
Als je in mysql insert met waarde '', is dat een lege string maar geen NULL.
Verander WHERE ba_id= ? AND cvt.cvt_TS_wat IS NOT NULL in:
Code (php)
1
WHERE ba_id= ? AND cvt.cvt_TS_wat <> ''
Maar Ger ik heb nu GROUP BY gewoon op de foute kolom GROUP BY cvt.cvt_TS_wat laten staan,
maar in output bij phpmyadmin zie ik geen verschil of foutmelding hoe je het ook noemen wilt..
Waarom is het zo belangrijk om GROUP BY te gebruiken op alles behalve de aggegrate kolommen terwijl het nu zonder ook werkt?
Tweede vraag: Hoor je GROUP BY dan te gebruiken wanneer er juist aggegrate kolommen er bij komen kijken?
Bedankt en fijne avond toegewenst!
Gewijzigd op 03/02/2014 20:09:11 door Francoi gckx
1) Omdat je anders onverwachte resultaten kan krijgen.
2) Als de databasebeheerder de systeem variabele FULL_GROUP_BY op 1 heeft gezet krijg je een foutmelding.
>>Tweede vraag: Hoor je GROUP BY dan te gebruiken wanneer er juist aggegrate kolommen er bij komen kijken?
Ja, omdat anders de GROUP BY zinloos is. Je ziet wel vaker voorbeelden voorbij komen waarbij een GROUP BY wordt gebruikt om DISTINCT values te verkrijgen (dus zonder agg functies), maar je kan dan veel beter SELECT DISTINCT gebruiken.
Het waarom is heel simpel:
SELECT DISTINCT slaat alle rows over die al eens geselecteerd zijn, terwijl bij group by eerst alle rows geselecteerd worden en dan pas gegroepeerd
Hij gooit nu alle id's eruit en sql houdt niks meer over
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
bcv.bcv_id, bcv.bcv_laatste_functie, bcv.bcv_beschrijving,
GROUP_CONCAT(CONCAT(cvt.cvt_TS_wat, '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_TS_wat,
GROUP_CONCAT(CONCAT(cvt.cvt_TS_hoelang, '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_TS_hoelang,
GROUP_CONCAT(CONCAT(cvt.cvt_technisch_know_how , '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_technisch_know_how ,
cvs.cvs_S_wat, cvs.cvs_S_hoelang, cvs.cvs_scholing, cvw.cvw_WE_wat, cvw.cvw_WE_hoelang, cvw.cvw_WE_inzet, cvw.cvw_WE_bedrijf, cvw.cvw_werkervaring, ba_id, bcv.bcv_datum, bcv.bcv_foto FROM beheer_cv bcv
INNER JOIN cv_techskills cvt
ON cvt.bcv_id = bcv.bcv_id
INNER JOIN cv_scholing cvs
ON
cvs.bcv_id = bcv.bcv_id
INNER JOIN cv_werkervaring cvw
ON
cvw.bcv_id = bcv.bcv_id
WHERE ba_id= 5 AND cvt.cvt_TS_wat <> ''
AND cvt.cvt_TS_hoelang <> '' AND cvt.cvt_technisch_know_how <> ''
GROUP BY bcv.bcv_id, bcv.bcv_laatste_functie, bcv.bcv_beschrijving, cvs.cvs_S_wat, cvs.cvs_S_hoelang, cvs.cvs_scholing, cvw.cvw_WE_wat, cvw.cvw_WE_hoelang, cvw.cvw_WE_inzet, cvw.cvw_WE_bedrijf, cvw.cvw_werkervaring, ba_id, bcv.bcv_datum, bcv.bcv_foto
bcv.bcv_id, bcv.bcv_laatste_functie, bcv.bcv_beschrijving,
GROUP_CONCAT(CONCAT(cvt.cvt_TS_wat, '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_TS_wat,
GROUP_CONCAT(CONCAT(cvt.cvt_TS_hoelang, '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_TS_hoelang,
GROUP_CONCAT(CONCAT(cvt.cvt_technisch_know_how , '/'), cvt.cvt_id ORDER BY cvt.cvt_id SEPARATOR '| ') AS cvt_technisch_know_how ,
cvs.cvs_S_wat, cvs.cvs_S_hoelang, cvs.cvs_scholing, cvw.cvw_WE_wat, cvw.cvw_WE_hoelang, cvw.cvw_WE_inzet, cvw.cvw_WE_bedrijf, cvw.cvw_werkervaring, ba_id, bcv.bcv_datum, bcv.bcv_foto FROM beheer_cv bcv
INNER JOIN cv_techskills cvt
ON cvt.bcv_id = bcv.bcv_id
INNER JOIN cv_scholing cvs
ON
cvs.bcv_id = bcv.bcv_id
INNER JOIN cv_werkervaring cvw
ON
cvw.bcv_id = bcv.bcv_id
WHERE ba_id= 5 AND cvt.cvt_TS_wat <> ''
AND cvt.cvt_TS_hoelang <> '' AND cvt.cvt_technisch_know_how <> ''
GROUP BY bcv.bcv_id, bcv.bcv_laatste_functie, bcv.bcv_beschrijving, cvs.cvs_S_wat, cvs.cvs_S_hoelang, cvs.cvs_scholing, cvw.cvw_WE_wat, cvw.cvw_WE_hoelang, cvw.cvw_WE_inzet, cvw.cvw_WE_bedrijf, cvw.cvw_werkervaring, ba_id, bcv.bcv_datum, bcv.bcv_foto