We help IT Professionals succeed at work.

how do i combine 2 selects

hi, i have an storeprocedure that returns 1 row with 2 columns, i want to combine with another select. like this:

table 1
id        name
1         bob
2         tom


select t1.*, getvalues(t1.id) from table1

so this query above does not work because the getvalues return more than one column. Any idea how to make this work?
Comment
Watch Question

Top Expert 2011

Commented:
I think you will have to change your other query to return more than one column and then use UNION ALL

Like:

Select column1, column2 from one table

UNION ALL

select column1, NULL from the other table.

Remember that when using usion, you must have same number of columns in both selects and the columns must be of *same* datatype.

That's how I would do it.

Someone else might have a better idea.

Commented:
is getValues table valued function?

in that case try like this

select t1.*, t2.* from table1 t1
outer apply getvalues(t1.id) as t2

Open in new window



Author

Commented:
can use an storeprocedure like that on the outer apply or just an function. because my object is an storeprocedure not an function?
Commented:
no you can not use stored procedures in outer apply. change it to table valued function.

Author

Commented:
ok, i did like you said, but one small problem the t1 table has a lots o rows that also needs to execute the outer apply, and it seems that is not permitted, how we can do that if t1 has a lots of rows?

Commented:
don't  understand what is the problem after changing it to function. can you post the function?

Author

Commented:
if the t1 table on your function has more than one row, than this error shows up. but if i put like this works fine:

select t1.*, t2.* from table1 t1
outer apply getvalues(t1.id) as t2
where t1.id = 1

because selects only one row. i need to work with more than one?

Explore More ContentExplore courses, solutions, and other research materials related to this topic.