Problems with SQL query

Question

I'm trying to find matches where the referee in the match (who is an official) also provides medical services to both the home and away teams (please refer to below relational diagram).

I've tried this, but it returns no records:

select matches.id,
matches.home,
matches.away,
matches.referee
from matches
join officials on officials.staffid = matches.referee
join medicalservices on medicalservices.team in (matches.home, matches.away)
where medicalservices.team = matches.home
and medicalservices.team = matches.away;

I'm guessing it doesn't return records because of the last two lines, but I don't know how else I can ensure that the same official provides medical services to both teams playing.

Here is the relational diagram for reference:

enter image description here

Brandon · Accepted Answer · 2013-05-10 21:03:51Z

1

You need to join medical services twice, once to find services provided to the home team and again to find services provided to the away team. Then you can check if services provided to each team were by the same referee.

You also do not need the officals table.

select matches.id,
matches.home,
matches.away,
matches.referee
from matches
join medicalservices home_medservices on home_medservices.team  = matches.home
join medicalservices away_medservices on away_medservices.team = matches.away
where home_medservices.official = matches.referee
  and away_medservices.official = matches.referee

edited May 10, 2013 at 21:03

answered May 10, 2013 at 20:55

Brandon

10.1k3 gold badges31 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martin Over a year ago

Neither of these give the expected outputs.

Brandon Over a year ago

@Tangler Sorry, try my updated answer. I didn't read your join clause before and assumed it was right.

Collectives™ on Stack Overflow

Problems with SQL query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related