SQL select query

Dear expert
Please help with a select query
i got a multiple select query like:
select * from data.test where id = 1245
select * from data.test2 where id = 1245
select * from data.test5 where id = 1245
I would like them to be in a column instead of multiple results windows shows up.
i tried:
Select (
select * from data.test where id = 1245
select * from data.test2 where id = 1245
)
Didnt work that way. Thx.
LVL 1
WeTiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
You could use UNION.

SELECT * FROM DATA.TEST WHERE ID = 1245
UNION

SELECT * FROM DATA.TEST2 WHERE ID = 1245
UNION

SELECT * FROM DATA.TEST5 WHERE ID = 1245
0
WeTiAuthor Commented:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
0
NorieAnalyst Assistant Commented:
Is that an error message you are receiving?

If it is it means that the tables don't have the same number of fields.

Which fields, that are common to all the tables, do you want to return?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

WeTiAuthor Commented:
I think the union only works when the result is one row (Or column?) some of the select returns with multiple rows.
0
Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
I think the union only works when the result is one row (Or column?) some of the select returns with multiple rows.
Union only works with the same number and type of columns. The number of rows is unimportant. If two rows are the same, only one will be returned so if you want all rows, you can use UNION ALL.

If you want them to be on a single row, you need to either use some form of pivot or construct the columns of the result set by joining the different tables.
0
WeTiAuthor Commented:
I only want them to show in a single table.
0
Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
I don't know what columns are in your tables, so for example let's say that test, test2 and test5 all have columns id and result, both numbers. You could do something like

select
  t.result as test_result, t2.result as test2_result, t5.result as test5_result
from
  data.test1 t 
  left join data.test2 t2 on t.id = t2.id
  left join data.test5 t5 on t.id = t5.id
where
  t.id = 1245

Open in new window


This may not be SQL server syntax so you may need to tweak it a little. I have not had chance to run it, so if any errors are reported, feel free to post them back here.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WeTiAuthor Commented:
Thanks for the help, but why cant you use * ? I don't really care about what columns to show up tho.
0
Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
You could use * in my above example. You will just see column data that is potentially irrelevant. For example, why show each test table's ID field, since they will always be the same and will always be the value that you supply as a parameter?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.