convert subquery to use Cases and Join-CodePudding

I have a query which is working fine. I want to convert it to use Cases and Join.

SELECT id, Ticket_id, serial_no, operator,
    (SELECT value FROM Forms_Details where FORM_ID = cv.id and status = 'ACTIVE' and key = 'COMPUTER_TYPE') as COMPUTER_TYPE,
    (SELECT value FROM Forms_Details where FORM_ID = cv.id and status = 'ACTIVE' and key = 'BRAND') as BRAND,
    (SELECT value FROM Forms_Details where FORM_ID = cv.id and status = 'ACTIVE' and key = 'MODEL') as MODEL,
    status, datetime
FROM Form cv
where status = 'ACTIVE' order by id desc;

CodePudding user response：

If you want to join, then join. What keeps you from doing so?

SELECT
  f.id,
  f.Ticket_id,
  f.serial_no,
  f.operator,
  ct.value AS computer_type,
  b.value AS brand,
  m.value AS model,
  f.status,
  f.datetime
FROM form f
LEFT JOIN forms_details ct ON ct.form_id = f.id AND ct.status = 'ACTIVE' AND ct.key = 'COMPUTER_TYPE'
LEFT JOIN forms_detailswhere b ON b.form_id = f.id AND b.status = 'ACTIVE' AND b.key = 'BRAND'
LEFT JOIN forms_detailswhere m ON m.form_id = f.id AND m.status = 'ACTIVE' AND m.key = 'MODEL'
WHERE f.status = 'ACTIVE' 
ORDER BY f.id DESC;

Or, in order not to read the same table twice, you can use conditional aggregation to get the values from the table:

SELECT
  f.id,
  f.Ticket_id,
  f.serial_no,
  f.operator,
  ct.value AS computer_type,
  fdw.brand,
  fdw.model,
  f.status,
  f.datetime
FROM form f
LEFT JOIN forms_details ct ON ct.form_id = f.id AND ct.status = 'ACTIVE' AND ct.key = 'COMPUTER_TYPE'
LEFT JOIN 
(
  SELECT
    form_id,
    MAX(CASE WHEN key = 'BRAND' THEN value END) AS brand,
    MAX(CASE WHEN key = 'MODEL' THEN value END) AS model
  FROM forms_detailswhere
  WHERE status = 'ACTIVE' AND key IN ('BRAND', 'MODEL')
  GROUP BY form_id
) fdw ON fdw.form_id = f.id
WHERE f.status = 'ACTIVE' 
ORDER BY f.id DESC;

CodePudding user response：

You can PIVOT and then join:

SELECT cv.id,
       cv.Ticket_id,
       cv.serial_no,
       cv.operator,
       fd.computer_type,
       fd.brand,
       fd.model,
       cv.status,
       cv.datetime
FROM  Form cv
      LEFT OUTER JOIN (
        SELECT *
        FROM   (
          SELECT form_id, key, value
          FROM   Form_Details
          WHERE  status = 'ACTIVE'
        )
        PIVOT (
          MAX(value)
          FOR key IN (
            'BRAND' AS brand,
            'MODEL' AS model,
            'COMPUTER_TYPE' AS computer_type
          )
        )
      ) fd
      ON (cv.id = fd.form_id)
where cv.status = 'ACTIVE'
order by id desc;