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.

Answer 1

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