SQL select query

WeTi
WeTi used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Author

Commented:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I think the union only works when the result is one row (Or column?) some of the select returns with multiple rows.
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
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.

Author

Commented:
I only want them to show in a single table.
Software Developer / Linux System Administrator / Managing Director
Commented:
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.

Author

Commented:
Thanks for the help, but why cant you use * ? I don't really care about what columns to show up tho.
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
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?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial