CODEWITHSUNDEEP
sqlt-sql
shin
shin

Reputation: 3649

Joining tables with case statement

I have been trying to join 4 tables with case statement on its 4th table Unfortunately my data was doubled and it is not the latest data on each table. Please help.

QUERY : 

SELECT
d.serial_no, d.dummy_serial ,
a.model_id, b.model_id, c.model_id, d.model_id,
MAX(a.create_date), MAX(b.create_date), MAX(c.create_date), MAX(d.create_date)
FROM table1 as a
JOIN table2 as b
ON a.serial_no = b.serial_no
JOIN table3 as c
ON b.serial_no = c.serial_no
JOIN table4 as d
ON c.serial_no =
(CASE WHEN
a.model_id = 'LB31' AND
b.model_id = 'LB31' AND
c.model_id = 'LB31' AND
d.model_id = 'LB31'
THEN d.dummy_serial
ELSE d.serial_no END );

WHAT I WANT

enter image description here

Upvotes: 0

Views: 48

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

The query was missing a group by clause. All the columns not being aggregated and in the select should be grouped.

SELECT
d.serial_no, d.dummy_serial ,
a.model_id, b.model_id, c.model_id, d.model_id,
MAX(a.create_date), MAX(b.create_date), MAX(c.create_date), MAX(d.create_date)
FROM table1 as a
JOIN table2 as b
ON a.serial_no = b.serial_no
JOIN table3 as c
ON b.serial_no = c.serial_no
JOIN table4 as d
ON c.serial_no =
(CASE WHEN
 a.model_id = 'LB31' AND
 b.model_id = 'LB31' AND
 c.model_id = 'LB31' AND
 d.model_id = 'LB31'
 THEN d.dummy_serial
 ELSE d.serial_no END)
group by d.serial_no, d.dummy_serial, a.model_id, b.model_id, c.model_id, d.model_id

Upvotes: 1

Related Questions