Skip to content
Advertisement

how to query get value without id

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
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement