Add to query from sub 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 2000 Forums

Transact-SQL (2000)

Add to query from sub query

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-01-29 : 18:39:13

Hi,

Is it possible to add to a result set with a subquery?

For example, imagine if I was selecting a set of categories and I also wanted to select products for each of the selected categories within the same query.

I suppose this would be a correlated union!

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-29 : 19:25:07

You would need to repeat the subquery in the union.
Might be faster to use a temp table or table variable.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-01-29 : 20:22:14

Thanks for your reply.

quote:
You would need to repeat the subquery in the union.

How do you mean?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-29 : 20:35:23

if you want them in different rows
select CategoryName, ProductName = ''
from categories
where category_id between(1 and 12)
union all
select CategoryName, ProductName
from categories c
join Product p
on p.Category_id = c.Category_id
where c.category_id between(1 and 12)

if you just want the categories and products then just the latter query in the union

Maybe some more info about what you are looking for and a short example.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources