Select from a subquery - 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

General SQL Server Forums

New to SQL Server Programming

Select from a subquery

Author

Topic

Noncentz
Starting Member

5 Posts

Posted - 2011-07-22 : 12:48:44

Good day everyone,

I am fairly new to SQL and I am trying to rewrite a query using a subquery. Up until now I would save my SELECT's as views and reference them in more views, so I would end up with 5 views for one report which is pretty horrible and inefficient.

Here is my query. I would like to select columns from the view 'dbo.p21_view_inv_loc' AND from 'dbo.p21_view_inv_period_usage'. The query is noncorrelated at this point so my outer query doesn't bind when I try to add records from the subquery. Is this possible or am I over/under/misunderstanding thinking this?


SELECT IL.qty_on_hand, IL.inv_min

FROM dbo.p21_view_inv_loc

WHERE EXISTS 

(

SELECT     
pu.item_id, 
pu.location_id, 
pu.inv_period_usage, 
pu.scheduled_usage, 
pu.inv_period_usage + pu.scheduled_usage AS actual_demand, 
pu.number_of_orders, 
pu.demand_period_uid, 
pu.inv_mast_uid, 
pu.inv_period_usage_uid, 
dp.period, 
dp.year_for_period, 
dp.computed_year_period AS year_and_period, 
dp.computed_year_period AS year_period

FROM         
dbo.p21_view_inv_period_usage AS pu INNER JOIN
dbo.p21_view_demand_period dp ON pu.demand_period_uid = dp.demand_period_uid

-- location delimiter
WHERE pu.location_id = '3000'

)

I tried this in the outer query but it seems recursive because I could simply use a JOIN for this:


SELECT IL.qty_on_hand, IL.inv_min, pu.item.id

FROM dbo.p21_view_inv_loc AS IL INNER JOIN
dbo.p21_view_inv_period_usage pu ON dbo.p21_view_inv_period_usage.location_id = IL.location_id

Any help is greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-22 : 12:55:29

you've not specified how they're related. what exactly you want to retrieve from them and based on what relationship?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Noncentz
Starting Member

5 Posts

Posted - 2011-07-22 : 13:02:11

Well I think I might have figured out where I went wrong here. I think I am not supposed to be using a subquery for this at all! My bad....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-22 : 13:09:48

yep. I feel its matter of simple join.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources