rafaelrgl
asked on
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?
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?
is getValues table valued function?
in that case try like this
in that case try like this
select t1.*, t2.* from table1 t1
outer apply getvalues(t1.id) as t2ASKER
can use an storeprocedure like that on the outer apply or just an function. because my object is an storeprocedure not an function?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
don't understand what is the problem after changing it to function. can you post the function?
ASKER
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?
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?
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.