0

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?

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.