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?