hokster23 - 1 year ago 57
MySQL Question

# Join for table using foreign keys

I have a table called player_league that looks like this:

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

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.

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.*