consolidate two fields in two different records to one record in JOIN

[iluvwitter]

Hi,

I am trying to set up a massive query with many joins and case statements. I'm noticing that my results are duplicating and can see why, but not sure the best way to fix it.

To simplify it, let's say I have two tables. Table A is orders and Table B is sub-orders. Table A doesn't have one record for each unique order, but can have multiple records for one order with different statuses. Table B are all sub-orders that fall under one order in Table A. I'm looking to return the timestamp where status = 'A' as TIME1 and timestamp where status = 'B' as TIME2 from table A for EACH sub-order in TableB.

Let's say data is:

TableA
A.ID Time orderid status
1 10:00 1 a
2 11:00 1 b

TableB
B.ID orderid amt
1 1 500
2 1 666
3 1 777
4 1 777
5 1 888

I want to return:

Time1 Time 2 orderid amt
10:00 11:00 1 500
10:00 11:00 1 666
10:00 11:00 1 777
10:00 11:00 1 777
10:00 11:00 1 888


How would I easily do this to get both times for each record in TableB where TableA.orderid = TableB.id? I don’t want to get 10 records back (5 record with time where status = a, 5 records with time where status = b), just the 5 with both times on each record. I'm actually joining with several tables, but this is to simplify it.

Thanks a lot!!

 

[markros]

If each order always have only 2 records with statuses A and B, then you would need to use self-join, e.g.

select O1.OrderID, O1.Time as Time1, O2.Time as Time2, SO.Amt from Orders O1 inner join Orders O2 on O1.OrderID = O2.OrderID and O1.Status = 'A' and O2.Status = 'B' inner join SubOrders SO on O1.OrderID = SO.OrderID

 

[iluvwitter]

thanks markros!! this worked!

 

[markros]

Great.