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 ;