SQL join multiple tables with one column becoming two

Question

I have 3 tables that I want to join. One table has fields that need to be broken out of one column to become two columns. Here are sample tables.

Doors

DoorID	DoorType
1	A
2	A
3	B
4	B
5	A
6	A
7	A

Parts

PartID	DoorID	Description	MaterialID
1	1	Hinge	1
2	1	Hinge	1
3	2	Hinge	1
4	2	Hinge	1
5	3	Hinge	1
6	3	Hinge	1
7	1	Plate	3
8	1	Plate	3
9	2	Plate	3
10	2	Plate	3
11	3	Plate	3
12	3	Plate	3
13	4	Plate	3
14	4	Plate	3
15	5	Hinge	2
16	5	Hinge	2
17		Deck	33
18		Unfinished Left End	33
19		Partition	38
20	5	Plate	4
21	5	Plate	4

Materials

MaterialID	Name
1	3/8 Hinge
2	5/8 Hinge
3	3/8 Plate
4	5/8 Plate
33	1/2 Birch Ply
38	3/4 Birch Ply

What I'd like to end up with is a count of each door type and what hinge and hinge plate is on each door type like the following. All of the A doors are counted together except the one that has no hinges. All of the B doors are counted together. Any part that's not a hinge or plate is ignored. Any material that's not a hinge or plate is ignored. Any door type that has no hinge or plate leaves those fields blank.

Door Count with Hinge Data:

Qty	Hinge	Plate
4	3/8 Hinge	3/8 Plate
2	5/8 Hinge	5/8 Plate
1

I've been beating my head against a wall on this for hours and am not getting anywhere. I'm very new to SQL as well. I have discovered that the application I'm using doesn't support CASE statements at all, but it can use IIF functions.

Any help would be much appreciated.

I guess you are using DB which is MYSQL and not SQL , looking at your restrictions with CASE, in MYSQL, CASE statement is allowed only in stored procedures. I will try and come up with an answer to your problem soon, meanwhile please verify your DB and update the tag accordingly because specific tags attract appropriate audience. — GSM, Jan 26 '22 at 05:20
@GSM: SQL is quite separate from (MS) SQL Server. I agree the question should be tagged with the DBMS in use. However, the SQL tag is generic for any DBMS which uses SQL as the query language; there are specific tags for SQL Server (quite a lot of them, many corresponding to release versions). — Jonathan Leffler, Jan 26 '22 at 07:31

dcolazin · Accepted Answer · 2022-01-26T12:42:44.867

0

You can try something like this

SELECT 
    count(DoorID) as Qty, Hinge, Plate 
FROM 
    ( SELECT D.DoorID as DoorID,
        group_concat(distinct IF(PH.Description = 'Hinge', MH.Name, NULL)) as Hinge, 
        group_concat(distinct IF(PP.Description = 'Plate', MP.Name, NULL)) as Plate
        FROM Doors as D
        LEFT JOIN Parts as PH
            ON D.DoorID = PH.DoorID
        LEFT JOIN Materials as MH
            ON PH.MaterialID = MH.MaterialID
        LEFT JOIN Parts as PP
            ON D.DoorID = PP.DoorID
        LEFT JOIN Materials as MP
            ON PP.MaterialID = MP.MaterialID
    GROUP BY D.DoorID ) as subquery
GROUP BY Hinge, Plate

like how is done in those answers.

edited Jan 26 '22 at 12:42

answered Jan 26 '22 at 06:46

dcolazin

831
1
10
25

1

Thanks. This was the biggest help in figuring out how to get what I wanted, though I had to do it a little different. The main thing I was missing was the ability to use two different aliases for the same table. My solution was to create a table joining Parts and Materials in a different query, then aliasing that table twice to pull the hinge name and plate name out into different columns. This avoided the need for any CASE or IIF since I could just use WHILE to filter the first query and a second ON condition to separate the hinge names and plate names during the join in the second query. – JustusCabinets Feb 01 '22 at 19:56

score -1 · Answer 2 · answered Jan 26 '22 at 06:52

Since you discovered your application has problem with CASE, will try and device a query with alternative IIF statement

SELECT SUM(D.DoorType) AS Qty,     
    IIF( P.Description = 'Hinge', M.Name , '') AS Hinge,       
    IIF( P.Description = 'Plate', M.Name , '') AS Plate       
FROM Doors D , Materials M , Parts P     
WHERE D.DoorID = P.DoorID     
    AND P.MaterialID = M.MaterialID     
    AND P.Description IN('Hinge' , 'Plate')    
GROUP BY D.DoorType
ORDER BY D.DoorType

SQL join multiple tables with one column becoming two

2 Answers2