Multiple Select Staments - 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 2005 Forums

Transact-SQL (2005)

Multiple Select Staments

Author

Topic

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-01-21 : 08:31:07

Is it possible to have 2 seperate select queries, but have them display in the same result? Here is an example that I would like to display together:

select item.item,item.description,itemwhse.qty_on_hand
from item
inner join itemwhse on item.item = itemwhse.item
where itemwhse.qty_on_hand > 0 and
item.serial_tracked = 1

select
poitem.item,
item.description,
poitem.qty_ordered
from po
inner join poitem on poitem.po_num = po.po_num
inner join item on poitem.item = item.item
where poitem.stat = 'O' and item.plan_code = 'IM1'

THanks for the help

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-21 : 08:33:27

place an UNION ALL between your two SELECT statements and you will get ONE resultset if the columns are fitting.

No, you're never too old to Yak'n'Roll if you're too young to die.

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-01-21 : 08:47:32

That is perfect. One other question, is there a way to have the colums seperate where needed? Example the first select has a qty_on_hand and the second select has a qty_ordered, but it appears both are falling under the qty_on_hand column?

Thanks,
Chad

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-21 : 09:00:12

If you want to get ONE resultset then how can it be possible to have different column names?

Do you have an example on how the result set should look like?

No, you're never too old to Yak'n'Roll if you're too young to die.

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-01-21 : 09:09:52

I might be going about this wrong. I will try to explain what I am trying to do. I want to search a couple tables (items, and itemwhse) with criteria (serialized and on hand) and then I want to search 2 other tables (po and poitem) for items on order that are seriliazed, so there is some linking between the 2 but not completly. So on the first search, I want to display item, description and qty on hand from tables item and itemwhse. The second search, I want to display item, description and qty on order from the poitem table, but I want them in the same results? Make sense?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-21 : 09:17:48

Try this:
select item.item,item.description,itemwhse.qty_on_hand,null as qty_ordered
from item
inner join itemwhse on item.item = itemwhse.item
where itemwhse.qty_on_hand > 0 and
item.serial_tracked = 1
UNION all
select
poitem.item,
item.description,
null as qty_on_hand,
poitem.qty_ordered
from po
inner join poitem on poitem.po_num = po.po_num
inner join item on poitem.item = item.item
where poitem.stat = 'O' and item.plan_code = 'IM1'

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources