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 :
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