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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Two Table? Join, Union ??
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 02/24/2012 :  10:17:48  Show Profile  Reply with Quote
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.

Not Just a Number

15586 Posts

Posted - 02/24/2012 :  10:23:24  Show Profile  Reply with Quote
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



Hint: Want your questions answered fast? Follow the direction in this link

Want to help yourself?

Go to Top of Page

Starting Member

2 Posts

Posted - 02/24/2012 :  10:37:43  Show Profile  Reply with Quote
That worked thank you very very much
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000