I want to get the value of the column regardless of the id of the table, because I want to use GROUP BY
to concatenate them
from the table :
id_child | id_master | test1 | test2 | test3 |
---|---|---|---|---|
1 | 1 | 1 | 1 | 0 |
2 | 1 | 1 | 0 | 0 |
3 | 1 | 1 | 0 | 0 |
4 | 2 | 1 | 0 | 0 |
5 | 2 | 1 | 0 | 0 |
6 | 2 | 1 | 0 | 0 |
7 | 3 | 1 | 0 | 0 |
8 | 3 | 1 | 0 | 1 |
9 | 3 | 1 | 0 | 0 |
Expected result:
id_master | test1 | test2 | test3 |
---|---|---|---|
1 | 1 | 1 | 0 |
2 | 1 | 0 | 0 |
3 | 1 | 0 | 1 |
My query :
x
SELECT
cd.id_cutting_detail,
CD.id_cutting,
IF(cd.outermold_barcode IS NULL, '0', '1') AS `outer`,
IF(cd.midmold_barcode IS NULL, '0', '1') AS `mid`,
IF(cd.linningmold_barcode IS NULL, '0', '1') AS `linning`
FROM
cutting_detail cd
GROUP BY
cd.id_cutting
This is what current query generates:
id_master | test1 | test2 | test3 |
---|---|---|---|
1 | 1 | 1 | 0 |
2 | 1 | 0 | 0 |
3 | 1 | 0 | 0 |
Advertisement
Answer
SELECT
id_cutting,
`outer`,
`mid`,
`lining`
(
SELECT
cd.id_cutting,
IF(cd.outermold_barcode IS NULL, '0', '1') AS `outer`,
IF(cd.midmold_barcode IS NULL, '0', '1') AS `mid`,
IF(cd.linningmold_barcode IS NULL, '0', '1') AS `linning`
FROM
cutting_detail cd
)
AS nulls_checked
GROUP BY
id_cutting,
`outer`,
`mid`,
`lining`
Or possibly…
SELECT
cd.id_cutting,
MAX(IF(cd.outermold_barcode IS NULL, '0', '1')) AS `outer`,
MAX(IF(cd.midmold_barcode IS NULL, '0', '1')) AS `mid`,
MAX(IF(cd.linningmold_barcode IS NULL, '0', '1') ) AS `linning`
FROM
cutting_detail cd
GROUP BY
cd.id_cutting