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
 General SQL Server Forums
 New to SQL Server Programming
 Two Table? Join, Union ??

Author  Topic 

Dcalladi
Starting Member

2 Posts

Posted - 2012-02-24 : 10:17:48
I have two tables Test_items and Stock_levels
i want to get the name from the Test_items and the current stock , min sotck and max stock from Stock_levels and the condition is that current stock need to be smaller than 1

i tried

SELECT Test_items.Name, Stock_levels.Current_Stock, Stock_levels.Min_Stock, Stock_levels.Max_Stock
FROM Test_items , Stock_levels
WHERE Stock_levels.Item_Id = (SELECT Item_id FROM Stock_levels WHERE Current_Stock < 1) AND
Stock_levels.Item_Id = Test_items.Id

this gives me #1242 - Subquery returns more than 1 row

ovbiously becuase of the nested query. i am just wondring if there is a way to do it without a nested solution so it works or if there is just another way of doing it.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-24 : 10:23:24
SELECT t.Name, o.Current_Stock, o.Min_Stock, o.Max_Stock
FROM Test_items t
INNER JOIN Stock_levels o
ON o.Item_Id = t.Id
WHERE EXISTS (SELECT * FROM Stock_levels i WHERE i.Current_Stock < 1 AND i.Item_Id = o.Item_Id)

Without sample data and DDL it's only a guess

Read the hint link in my sig




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

Dcalladi
Starting Member

2 Posts

Posted - 2012-02-24 : 10:37:43
That worked thank you very very much
Go to Top of Page
   

- Advertisement -