How to combine results in cursor?
-
I have a query that returns a table something like this: 102 NULL 101 NULL the 101 & 102 are FKs into another table, and I need to "expand" those... so what I want to end up with is: 102 b 102 a 101 b 101 a so I figured I'd create a cursor on the first table and loop through the 102, 101, etc. and then do an inner select to "expand" the 101 and 102... this works fine... I end up with 4 result sets though. One for the 102, one for the first null, one of the 101 and one for the second null. The empty result sets are significant to the query... I figured I could insert a dummy record in there with a special ID... unless there is a better way, but not sure how to combine the 4 result sets into a single one with out losing the empty ones?
-
I have a query that returns a table something like this: 102 NULL 101 NULL the 101 & 102 are FKs into another table, and I need to "expand" those... so what I want to end up with is: 102 b 102 a 101 b 101 a so I figured I'd create a cursor on the first table and loop through the 102, 101, etc. and then do an inner select to "expand" the 101 and 102... this works fine... I end up with 4 result sets though. One for the 102, one for the first null, one of the 101 and one for the second null. The empty result sets are significant to the query... I figured I could insert a dummy record in there with a special ID... unless there is a better way, but not sure how to combine the 4 result sets into a single one with out losing the empty ones?
I would as a default try and avoid cursors, they really are a last resort tool. Have you tried UNION or UNION ALL on the 2 queries.
Select This, null That
from SomeTableUnion
Select This, That
from SomeTable
left join ThatTable on ...Never underestimate the power of human stupidity RAH
-
I have a query that returns a table something like this: 102 NULL 101 NULL the 101 & 102 are FKs into another table, and I need to "expand" those... so what I want to end up with is: 102 b 102 a 101 b 101 a so I figured I'd create a cursor on the first table and loop through the 102, 101, etc. and then do an inner select to "expand" the 101 and 102... this works fine... I end up with 4 result sets though. One for the 102, one for the first null, one of the 101 and one for the second null. The empty result sets are significant to the query... I figured I could insert a dummy record in there with a special ID... unless there is a better way, but not sure how to combine the 4 result sets into a single one with out losing the empty ones?
Dear you try....... you can reduce your null selection by is null clause. you can use union or union all are two method combine multiple results set. select 101 union select null union select 102 union select null union all result will be as... null, null 101 102 101 102 create table temp table insert into @temptable(ID)
select 101
union
select null
union
select 102
union
select nullselect * from @temptable where id is not null ......................... try this......