# Conditional join on a third table

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?

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 ;
``````
• 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. – ChrisF Aug 26 '16 at 10:50
• You could add 2 columns in my 1st query with simple `CASE` expressions. – ypercubeᵀᴹ Aug 26 '16 at 10:54
• 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. – ChrisF Aug 26 '16 at 11:03