Conditional Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Conditional Join

Author

Topic

SirRawlins
Starting Member

14 Posts

Posted - 2010-11-29 : 10:30:22

Hello Guys,

I hope I can explain this requirement effectively. I'll try to do so as simply as possible. I have two tables:

SaleOrder
--------
SaleOrder_ID
SaleOrder_Number

SaleOrderItem
-----------
SaleOrderItem_ID
SaleOrder_ID
Courier_ID
GoodsRecieved

Now, I want to return a list of 'SaleOrder_Number's, all of whom's SaleOrderItems which have Courier_ID NOT NULL have GoodsRecieved NOT NULL.

This will give me a list of orders, all of which's items that have a courier assigned have been received. If only _some_ of the order items have been received then they will not appear in the list. As orders who's items don't have a courier assigned will also not appear.

I hope that is clear enough, if anyone has any questions or is looking for more information then let me know.

Thanks,

Robert

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-29 : 11:49:19

Is this you are looking for:

Select SOT.*, SO.SaleOrder_number from
SaleOrder SO inner join SaleOrderItem SOT
On SO.SaleOrder_ID = SOT.SaleOrder_ID
and SOT.SaleOrder_ID not in --Filter the partially received goods
(Select SaleOrder_ID from SaleOrderItem SOInner where
SOInner.Courier_ID is null or SOInner.GoodsRecieved is null)

SirRawlins
Starting Member

14 Posts

Posted - 2010-11-29 : 12:32:23

Thanks for the quick reply!

I've run some basic tests and it appears to be returning just as expected! :-)

I think you hit the nail on the head, I'll give you a shout once I've run some more tests if things aren't right!

Thanks again.

Robert

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-29 : 22:37:17

quote:
Originally posted by SirRawlins

Thanks for the quick reply!

I've run some basic tests and it appears to be returning just as expected! :-)

I think you hit the nail on the head, I'll give you a shout once I've run some more tests if things aren't right!

Thanks again.

Robert

You are welcome

If at later stage you find that query is not returning as expected then please post some sample data and expected output.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources