Help with sql

[minli98]

Hi,

I have a simple table to keep track of marathon runners and their time in the events that they run in. The table has 3 fields: Runner's name, EventID, Run Time.

So, for example,

Name     EventId    Time
A        1          2:15
B        1          2:11
C        1          2:12
....
B        2          2:09
C        2          2:11
D        2          2:15
...
A        3          2:10
B        3          2:10
C        3          2:14
D        3          2:15
...
A        4          2:11
B        4          2:09
C        4          2:16
...

My question is how do I write a query to pull the name of the runners that participated in say, events 1, 2, 3 and 4? In the example above, the answer would be B and C since they are the only 2 that ran in all four events.

I'd appreciate help with writing this query. Thank you!

Regards,
M

 

[ca8msm]

You could use an exists clause and write a select query for each run e.g.

select id 
from table a
where exists (select id from table b where a.id = b.id and b.value = 1)
and exists (select id from table c where a.id = c.id and c.value = 2)
...etc

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[minli98]

Mark,

Thanks. I will give it a try.

Regards,
M

 

[r937]

select Name     
  from marathons
 where EventId in ( 1,2,3,4 )
group
    by Name
having count(*) = 4

sometimes you need to check COUNT(DISTINCT ...) instead of COUNT(*), but in this case it's not necessary as presumably the same guy cannot participate in the same event more than once




r937.com | rudy.ca

 

[ca8msm]

Use Rudy's example, not mine. It will be much faster.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]