Items in Ordered table not in Recieved Statement

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 2000 Forums

Transact-SQL (2000)

Items in Ordered table not in Recieved Statement

Author

Topic

vravo
Starting Member

13 Posts

Posted - 2004-10-11 : 17:38:01

Trying to figure this out, I have two tables, table 1 has orders I placed in it and the 2nd table has orders I Received. When orders are received not all items ordered are in the shipment....so I'm trying to write a Select Statement that will show what items from a specific order placed that where not received yet... For example here is a made up order with item number 5 missing from the order received... so I want my Query to produce the output below...
My attempt at this is at the bottom, it returns all items from the ordered table that are not from order '12345' where as I need them to only be from this order... any help would be appreciated...

vin

table ordered
=============
order_number | line_number | Total_qty | ITEM
------------------------------------------------------
12345 | 1 | 2 | item1
12345 | 2 | 4 | item2
12345 | 3 | 1 | item3
12345 | 4 | 6 | item4
12345 | 5 | 5 | item5

table Received
==============
order_number | line_number | Total_qty | ITEM
------------------------------------------------------
12345 | 1 | 2 | item1
12345 | 2 | 4 | item2
12345 | 3 | 1 | item3
12345 | 4 | 6 | item4

Query Output
============
order_number | line_number | Total_qty | ITEM
-------------------------------------------------------
12345 | 5 | 5 | item5

(only grabbing info I need from table)
SELECT a.order_number,a.line_number,a.qty,a.item
FROM Ordered a, Received b
WHERE not exists(SELECT * FROM Received b WHERE order_number = '12345' and a.item = b.item)

Thanks

Vin

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-11 : 18:22:02

SELECT a.order_number,a.line_number,a.qty,a.item
FROM Ordered a
WHERE not exists(SELECT * FROM Received b WHERE b.order_number = a.order_number and a.item = b.item)
and order_number = '12345'

SELECT a.order_number,a.line_number,a.qty,a.item
FROM Ordered a
left join Received b
on b.order_number = a.order_number and a.item = b.item
where a.order_number = '12345'
and b.order_number is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vravo
Starting Member

13 Posts

Posted - 2004-10-11 : 18:34:54

I see said the blind man....

Thanks

Vin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources