SQLTeam.com | Weblogs | Forums

Help needed for the query

sql2008r2

#1

I have two tables : Runner table with the following columns

ID Name


1 Michael Johnson
2 Mike Powell
3 Roger Federer
4 Mike Phelps

Winner table with the following columns

EventID EventName RunnerID

1 Long Jump 2
2 400M 1
3 ShortPut NULL
4 Swimming 4

I have to frame a query such that the Runner table records which are not present in Winner table has to be returned (without using any subquery or NOT IN clause)


#2

Sounds like you need NOT EXISTS then


#3

Can I use EXCEPT for this? or Is NOT EXISTS the better option? Also, for using NOT EXISTS, we need to write a sub query .right?


#4

I'd use left outer join with positive null


#5

Can you please write the query for easy understanding?


#6

Something like this:

Query
select a.id
      ,a.[name]
  from runner as a
       left outer join winner as b
                    on b.runnerid=a.id
 where b.id is null
;