SQL query doesnt return expected values-CodePudding

I have 3 tables. 1st table stores coil information, 2nd table store coil information in transport and 3rd stores reserved coils.

coils

ID	SERIAL	COLOR	MATERIAL	STATUS
1	12345	5	1	2
2	12346	4	1	3
3	12347	3	1	2

coils_in_transport

ID	SERIAL	COLOR	MATERIAL	STATUS
1	f34S5	5	1	2
2	A23GG6	4	1	3
3	ff2S147	3	1	2

reserved_coils

ID	NUMBER	QUANTITY	START	END
1	12345	25	2022-05-01	2023-05-01
3	12347	252	2022-01-01	2023-05-01
4	A23GG6	33	2022-04-01	2023-05-01
5	ff2S147	35	2022-08-01	2023-05-01

I need to write query that will get all reserved coils (reserved_coils) that has status 2 and for each coil I need to join tables for materials and colors

material

ID	NAME
1

color

ID	NAME

I wrote query but it doesn't show coils in transport that are reserved, here is what I tried

SELECT a.QUANTITY, a.START, a.END, b.name, m.name 
FROM reserved_coils a 
LEFT JOIN coils b ON a.number = b.serial
LEFT JOIN coils_in_transport c ON a.number = c.serial
LEFT JOIN material m ON b.material = m.id
LEFT JOIN material b ON b.material = b.id
where b.status = 2 and c.status = 2

CodePudding user response：

To get all coils and colis_in_transport you need to use UNION for SELECT statements for both tables, each filtered by status value, which should be equal to 2. Then you need to do a JOIN between reserved_coils and the result of UNION to filter out reserved_coils rows.

Your query would be like this

SELECT 
    r.quantity, 
    r.start, 
    r.[end], 
    c.serial, 
    material.name AS material, 
    color.name AS color
FROM reserved_coils r
JOIN (
    SELECT * FROM coils WHERE status = 2
    UNION
    SELECT * FROM coils_in_transport WHERE status = 2
) c ON r.number = c.serial
LEFT JOIN material ON material.id = c.material
LEFT JOIN color ON color.id = c.color

Demo

CodePudding user response：

If you want left join twice on same table, this might help

SELECT a.QUANTITY, a.START, a.[END], isnull(b.serial,c.serial) serial,isnull(c1.name,c2.name) color, isnull(m.name,n.name) material
FROM reserved_coils a 
LEFT JOIN coils b ON a.number = b.serial
LEFT JOIN coils_in_transport c ON a.number = c.serial
LEFT JOIN material m ON b.material = m.id
LEFT JOIN material n ON c.material = n.id
LEFT JOIN color c1 on b.color=c1.id
LEFT JOIN color c2 on c.color=c2.id
where isnull(b.status,c.status) = 2

DB<>Fiddle