2 Select Statements 1 output - 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

2 Select Statements 1 output

Author

Topic

robp74
Starting Member

5 Posts

Posted - 2008-07-24 : 14:28:26

Hi All,

I'm not sure how to explain this properly, but i'll give it a shot. I have written a script that pulls data from 3 different tables using 2 select statements. (see below) The result of the script is shown i 2 seperate tables. I am trying to get them into 1 table. I think you can do this by creating vrtual tables??, then combining them into 1?? Could someone please help!

SELECT item.item, itemprice.unit_price1, item.description, UF_Option_Group = 'Machine'

FROM item INNER JOIN
itemprice ON item.item = itemprice.item

where (item.item like '%T21i%')
order by item

SELECT jobmatl.item, itemprice.unit_price1,
jobmatl.description, jobmatl.Uf_OptionGroup

FROM job
INNER JOIN
jobmatl ON job.job = jobmatl.job
INNER JOIN
itemprice ON jobmatl.item = itemprice.item
WHERE (job.item = 't21ifl-10k')

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-24 : 14:35:21

Assuming the output of both queries are the same structure (columns and datatypes) you can UNION ALL the two statements:

SELECT statement 1
union all
SELECT statement 2

Be One with the Optimizer
TG

robp74
Starting Member

5 Posts

Posted - 2008-07-24 : 14:48:45

Unfortunately that won't work because the tables have different data. is there another way to do it? can you create virtual tables?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-24 : 15:15:52

What error do you get when you try the UNION ALL?

Be One with the Optimizer
TG

robp74
Starting Member

5 Posts

Posted - 2008-07-24 : 16:44:28

I figured out the problem!!! Union all did work. i just need to add the extra join. below is the completed script. thanks for all your help!!

select item.item, itemprice.unit_price1, item.description, uf_optiongroup = ' Machine' from item
inner join
itemprice ON item.item = itemprice.item
where item.item = @model
union all
SELECT jobmatl.item, itemprice.unit_price1,
item.description as [description], jobmatl.Uf_OptionGroup

FROM job
INNER JOIN
jobmatl ON job.job = jobmatl.job
INNER JOIN
itemprice ON jobmatl.item = itemprice.item
INNER JOIN
item ON jobmatl.item = item.item
WHERE job.item = @model
AND job.type = 'S'
AND jobmatl.UF_OptionGroup <> 'null'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources