help with LEFT JOINs

Anon

I'm using mysql.

I know how to do this with multiple queries but I'm trying to get it to all work in one query. I have three tables, described below.

registrations

registration_id
student_id
event_id
status

team_rosters (multiple students per team)

team_roster_id
team_id
student_id

teams (multiple teams per event)

team_id
event_id
team_name

A student that is registered may or may not be on a team. I'm trying to get the following:
Registration data for a student (based on student_id). For each event the student is registered, return the team name if he/she is on a team, if any. If not on a team, return NULL for team name.

I've got a query that comes close but no cigar.
SELECT regs1., team_rosters., teams.*
FROM registrations AS regs1 LEFT JOIN team_rosters USING (student_id),
registrations AS regs2 LEFT JOIN teams ON regs2.event_id=teams.event_id
WHERE regs1.student_id=20 AND regs2.student_id=20 AND team_rosters.team_id=teams.team_id

THANKS in advance!

Anon

I think my basic problem is that I'm think I want to use registrations in two different left joins.

#1: registrations LEFT JOIN team_rosters using (student_id)

#2: registrations LEFT JOIN teams using (event_id)

There is also an inner join between team_rosters.team_id and teams.team_id

I just can't successfully put all those together. I've come close but nothing that works right.

Anon

Wont this work?

SELECT regs1.,
team_rosters.,
teams.*
FROM registrations AS regs1
LEFT JOIN team_rosters USING (student_id)
LEFT JOIN teams ON team_rosters.team_id=teams.team_id
WHERE regs1.student_id=20

The thought being, the first left join does all the left joining for registrations and the second keeps the null if there\'s no team.

Anon

Unfortunately, no -- that didn't work.

I think I need two different LEFT JOINs.

#1: registrations LEFT JOIN team_rosters USING (student_id)

#2: registrations LEFT JOIN teams USING (event_id)

I have tried this:
SELECT regs1., team_rosters., teams.*
FROM registrations AS regs1
LEFT JOIN team_rosters USING (student_id)
LEFT JOIN teams using (team_id)
WHERE regs1.student_id=20 and
(regs1.event_id=teams.event_id or
teams.event_id is null)

That almost gets me there. But the problem is that each row from registrations gets duplicated for every row in teams.

Does that make sense?