Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cat_jesus
Aged Yak Warrior
547 Posts |
 Posted - 2002-06-24 : 14:17:34
|
| I have a SP that was creating a temp table to determine if no rows were returned so it could return nulls instead of zero rows. I rewrote it to use a union instead and I was wondering if anyone else had a better idea. I don't like this idea much either because it ends up running the selection query twice. here is a pubs version of the problem. Any ideas of a better way to do this without a temp table and without this type of union?use pubs select fname, minit, lname from employee where fname = 'zeppo'unionselect null fname, null minit, null lname from (select count(fname) as cnt from employee where fname = 'zeppo') as SL1 where SL1.cnt = 0 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-06-24 : 14:41:14
|
| Wouldn't say it's better but.select fname, minit, lname from (select a = 1) as tbl left outer join employee on fname = 'zeppo'==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2002-06-24 : 14:54:44
|
| OK this works but why does it work? I don't understand how the (select a=1) fits in. Unless it is somehow creating a null set, but if that is the case why does it drop the null set if there are values on the left join? |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-24 : 15:09:21
|
| Does this make it clearer?select fname, minit, lname from (select 'any value' as somecolumn) as tbl left outer join employee on fname = 'zeppo' Now my turn to ask a question. Why do you want a rowset of nulls?<O> |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-06-24 : 15:20:55
|
| It creates a single row table and left joins it to the result set you want.If your result set has rows then they are joined to the single row derived table.If your result set has no rows then the left join causes a null recordset to be joined to the derived table.In either case the derived table is omitted from the output and you just get your result set or a null row.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2002-06-24 : 15:27:18
|
| I don't someone else "needs" it for crystal reports. I just thought the temp table was unnecessary.I guess I oversimplified the query. there are some inner join that narrow the selection set returned so what I have is more like this but with a few more inner joins.select fname, minit, lname from employee e join authors a on e.fname = a.au_fname and e.fname = 'Zeppo'unionselect null fname, null minit, null lname from (select count(fname) as cnt from employee e join authors a on e.fname = a.au_fname and e.fname = 'Zeppo') as SL1 where SL1.cnt = 0 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-06-24 : 15:47:12
|
| You should be able to include whatever your query is as a derived table and outer join it to the single row derived table.You might find though that using the temp table is more flexible for future use.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
return nulls when zero records returned