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 |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2008-09-18 : 12:07:08
|
| I have two tables:select *from (select 1 Point union select 2 union select 3 union select 4) FullListselect *from (select 1 Point union select 3 union select 4) MissingHow do I write a query to return one record with the value "2" to indicate that that is the record number that is missing in the second table? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-18 : 12:33:53
|
| [code]SELECT q1.pointFROM (select *from(select 1 Pointunionselect 2unionselect 3unionselect 4) FullList)q1LEFT JOIN (select *from(select 1 Pointunionselect 3unionselect 4) Missing) q2ON q2.point=q1.pointWHERE q2.point IS NULL[/code] |
 |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2008-09-19 : 11:19:13
|
| I knew there was a left join in there somewhere. Set-based brain wasn't working that day... Thanks man! |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2008-09-30 : 10:03:38
|
| Instea dof the FullList table, could I have done something with the row_number syntax in 2005 to likewise generate a virtual table of n sequential numbers? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-30 : 10:09:13
|
quote:
Originally posted by coolerbob
Instea dof the FullList table, could I have done something with the row_number syntax in 2005 to likewise generate a virtual table of n sequential numbers?
you can. on basis of what field you want to sequence? |
|
|
|
|
|
|
Show missing record