Please help me with this horrible select statement!

[aaarrrggh]

I'm a newbie to MYSQL, and have been struggling with this one select statement for the past two days now, and I just can't seem to get it right. I'm pretty sure I should be using 'LEFT JOIN', but I don't know how to execute it properly.

I'm creating a soccer site for an online game that allows people to create their own leagues, and this select statement is meant to display all the teams in each league. Eventually it will need to display them in points order, then goal difference etc, but that's not the part that I'm worried about right now.

Basically, I'm storing information in several different tables to make it easier to create the leagues themselves.

I have a users table with all the user info in it, a 'teams' table, which stores the information about each assigned team, a 'team names' table which is used to store the actual names of the teams, a 'leagues' table, which is used to store information about assigned leagues, and finally a 'league rankings' table, which stores information about how well each team is doing in their respective league (points, goals scored, games won etc).

The select statement I've created so far looks like this:

"SELECT users.username, team_names.team_name FROM users, team_names,
teams, league_rankings, leagues WHERE users.user_id = teams.user_id
AND team_names.team_names_id = teams.team_names_id AND
league_rankings.idteams = teams.idteams AND leagues.league_id = 33
AND teams.league_id = leagues.league_id;"

(Where leagues.league_id is the actual league I want to look at, in this case being league 33). This seems to work fine in showing me all teams in a particular league that have been assigned a user, but I want to also be able to show the teams that have no user assigned too. Basically, the way I'm working it is that the teams exist inside the league table first, and a player then comes along and 'owns' that team.

Does anyone here know how I might be able to get all the teams in a particular league to show up? Sorry if I've not given enough information about my database structure; I can elaborate on this if that would help.

Please help me with this guys. I'm going mad here!

[fenway]

You are correct -- if you want to return a record from the tables no matter what, you need to use outer joins -- a LEFT JOIN is appropriate. Try the following (UNTESTED):

[code]SELECT
users.username, team_names.team_name
FROM users
LEFT JOIN teams ON ( users.user_id = teams.user_id )
LEFT JOIN team_names ON ( team_names.team_names_id = teams.team_names_id )
LEFT JOIN leagues ON ( teams.league_id = leagues.league_id )
LEFT JOIN league_rankings ON ( league_rankings.idteams = teams.idteams )
WHERE leagues.league_id = 33[/code]

[wickning1]

I don't think that will work, the WHERE clause will eliminate NULL values and all the left joins will be optimized away.

Also it sounds like he wants a team-centric query, showing the teams that have no user. So the teams table should come first. Try this:

[code]SELECT team_names.team_name, users.username
FROM teams
LEFT JOIN users ON ( users.user_id = teams.user_id )
LEFT JOIN team_names ON ( team_names.team_names_id = teams.team_names_id )
LEFT JOIN leagues ON ( teams.league_id = leagues.league_id)
LEFT JOIN league_rankings ON ( league_rankings.idteams = teams.idteams )
WHERE teams.league_id=33[/code]
Joining the leauges and league_rankings tables is not necessary with what you are selecting. Unless you plan to modify the query to retrieve data from them, you can use this query instead:

[code]SELECT team_names.team_name, users.username
FROM teams
LEFT JOIN users ON ( users.user_id = teams.user_id )
LEFT JOIN team_names ON ( team_names.team_names_id = teams.team_names_id )
WHERE teams.league_id=33[/code]

[fenway]

True enough, though it looks like the poster actually wants "all teams in a particular league", so the 2nd posted query is most appropriate.