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

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

Dcalladi
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.

X002548
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




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 - 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  
 New 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