T-SQL Query - NULL's

[cstrong]

Hi all,

Quick query problem...

I need to query the table to find if there are any partial shipments. If there is a partial shipment, then there will be no shipping name assigned (the field will contain a NULL).

So, if there are 10 lines on an order and 8 are picked, the 8 lines will have a picking name and 2 lines NULL.

What I want to do, is list all of orders that are not complete....ie, contain some picked lines and some lines that are not picked.

Thanks,
Clive

 

[tlbroadbent]

Assuming each record has an order number or order ID. You could do something like the following.

Select * From Orders
Where OrderID In (Select OrderID Where ShippingName Is Null) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Guest_imported]

This should do it (not tesed; haven't got my sql reference handy), assuming you have Orders and OrderLines tables related by OrderNo :
select * from Orders
where (case (exists select 1 from OrderLines where OrderLines.OrderNo = Orders.OrderNo) then 1 else 0)) = 1

The exists clause returns (an internal form of ) true as soon as the select it quantifies find a record and false if none are found. See the BOL.