Conditional join on a third table

Question

I have four tables - A, B, C and D.

Table A holds inventory movement records, table B holds sales records on a per item basis, table C holds non-sales records on a per item basis and table C holds the type of non-sales movement.

For each record in A there's either a line in B or a line in C. So to get a single row of the movements for each item I do the following:

select column1, column2, ... from A
left join B on B.Id = A.IdIntoB
left join C on C.Id = A.IdIntoC

So far so good. However, as I need to know what type of movement it was in C as I want to return the following:

ItemId, Date, Sales, Goods In, Returns to Supplier

i.e. have separate columns for good in and goods returned to a supplier.

The type (goods in or return to supplier) is stored in D and there is a many to one relationship between C and D as D is the "document" that groups the items in C.

If I do:

select column1, column2, ... from A
left join B on B.Id = A.IdIntoB
left join C as C1 on C1.Id = A.IdIntoC
 left join D on D.Id = C1.IdIntoD and D.Type = 1
left join C as C2 on C2.Id = A.IdIntoC
 left join D on D.Id = C2.IdIntoD and D.Type = 2

I just get duplicate rows as the two joins from A to C are effectively the same.

What I want to do is this (non-legal syntax):

select column1, column2, ... from A
left join B on B.Id = A.IdIntoB
left join C as C1 on C1.Id = A.IdIntoC and D.Type = 1
 left join D on D.Id = C1.IdIntoD
left join C as C2 on C2.Id = A.IdIntoC and D.Type = 2
 left join D on D.Id = C2.IdIntoD

Basically I want separate out the joins between A and C based on a value in D. I know that I have to use a left join between C and D each time otherwise I'll get no data, but logically that's what I feel I need to do.

How do I do this?

Answer 1

I don't think you need to join the C table twice. This would work:

select column1, column2, ... from A
left join B on B.Id = A.IdIntoB
left join C on C.Id = A.IdIntoC
 left join D on D.Id = C.IdIntoD and (D.Type = 1 OR D.Type = 2) ;

The (D.Type = 1 OR D.Type = 2) can be further simplified to D.Type in (1, 2).

---

If you do want 2 joins to C (and D), you could alter the syntax in your second to last query to (do an inner join to D) and move the join condition to the end:

select column1, column2, ... from A
left join B on B.Id = A.IdIntoB
left join C as C1 
 inner join D as D1 on D1.Id = C1.IdIntoD and D1.Type = 1
 on C1.Id = A.IdIntoC 
left join C as C2 
 inner join D as D2 on D2.Id = C2.IdIntoD and D2.Type = 2 
 on C2.Id = A.IdIntoC ;

or more simpler, join C once and D twice:

select column1, column2, ... from A
left join B on B.Id = A.IdIntoB
left join C on C.Id = A.IdIntoC
 left join D as D1 on D1.Id = C.IdIntoD and D1.Type = 1
 left join D as D2 on D2.Id = C.IdIntoD and D2.Type = 2 ;

• ChrisF: Surely this will give me the rows where D.Type is 1 or 2 combined. What I want is a column for where D.Type = 1 and second column where D.Type = 2.
• ypercubeᵀᴹ: You could add 2 columns in my 1st query with simple CASE expressions.
• ChrisF: Didn't think of using a CASE - though in reality there are about 5 columns for each of the goods in/goods out situations so that might get a bit messy in the select statement. The inner join route looks like the way to go.