Need help in query - 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)

Need help in query

Author

Topic

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-10-07 : 10:19:43

Hi All,

I want to find out the active items but there is no stock in the InventoryStock.

One item could have multiple receipts in the InventoryStock table.

SELECT I.*
FROM Items I
INNER JOIN ItemsOwnership I1
ON I.ItemID = I1.ItemID
AND I.LocID = I1.LocID
LEFT JOIN InventoryStock I3
on I1.LocID = I3.LocID
and I1.receipt_No = I3.receipt_No
where I3.receipt_No is null

But this sql shows me the items in my list if there is atleast one receipt for that item. I am interesting in seeing only those items where there is no stock in InventoryStocks table.

How would I change this SQL to achieve this result?

Thanks,
-S

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-10-07 : 11:29:35

Can someone please help me on this request?

asgast
Posting Yak Master

149 Posts

Posted - 2009-10-07 : 11:33:46

I will really hate myself for answering this :)

SELECT I.*
FROM Items I
INNER JOIN ItemsOwnership I1
ON I.ItemID = I1.ItemID
AND I.LocID = I1.LocID
INNER JOIN InventoryStock I3
on I1.LocID = I3.LocID
and I1.receipt_No = I3.receipt_No
where I3.receipt_No is null

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-10-07 : 11:39:40

Thanks Asgast for your reply.

If the item does not have stock it will not be there in the inventorystock table. By running your sql I still see those items because they have one or more receipts in the inventorystock table.

I wanted to list all the items which are not there in the inventorystock table but are there in the ItemsOwership table.

Any suggestion?

asgast
Posting Yak Master

149 Posts

Posted - 2009-10-07 : 11:55:05

I was completely wrong then

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-10-07 : 12:10:31

[code]
SELECT I.*
FROM Items I
LEFT JOIN
(
ItemsOwnership I1
JOIN InventoryStock I3
ON I1.LocID = I3.LocID
AND I1.receipt_No = I3.receipt_No
)
ON I.ItemID = I1.ItemID
AND I.LocID = I1.LocID
WHERE I3.receipt_No IS NULL

SELECT I.*
FROM ItemsOwnership I1
JOIN InventoryStock I3
ON I1.LocID = I3.LocID
AND I1.receipt_No = I3.receipt_No
RIGHT JOIN Items I
ON I.ItemID = I1.ItemID
AND I.LocID = I1.LocID
WHERE I3.receipt_No IS NULL

SELECT I.*
FROM Items I
WHERE NOT EXISTS
(
SELECT *
FROM ItemsOwnership I1
WHERE I.ItemID = I1.ItemID
AND I.LocID = I1.LocID
AND EXISTS
(
SELECT *
FROM InventoryStock I3
WHERE I1.LocID = I3.LocID
AND I1.receipt_No = I3.receipt_No
)
)
[/code]

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-10-07 : 13:10:44

Thanks Ifor. Why are there three SELECT statements? You lost me completely. Please giv emore details.

Sachin.Nand

2937 Posts

Posted - 2009-10-08 : 02:41:49

He gave you 3 solutions.

PBUH

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-10-08 : 09:00:24

You are Genius Ifor. I tried all three solution and they worked. Excellent!

Thank you,
-S