hokster23 hokster23 - 6 months ago 13
MySQL Question

Join for table using foreign keys

I have a table called player_league that looks like this: enter image description here

The userID and leagueID are foreign keys referencing columns in the users and leagues table respectively.

I want to query the database to get all the users who would have a leagueID of 1 from the player_leagues table.

This query

SELECT leagueID from users join player_league pl WHERE pl.leagueID = 1


returns data that looks like enter image description here

Being that I am new to mySQL and SQL in general, I was wondering what I was doing wrong? I believe it has something to do with my joins but it may have been how I set up my tables, I'm just not sure. There is no leagueID in the users table, so I'm not sure why each user would have a leagueID of 1.

Answer

You're missing the on part of the join clause that instructs the database how to join the two tables. The default is a Cartesian Product, i.e., joining every row from the first table with every row from the second table.

In this case, you can join on the userID:

SELECT u.*
FROM   users u
JOIN   player_league pl ON u.userID = pl.userID
WHERE  pl.leagueID = 1