MySQL get column of primary key reference table

  • mindfullsilence
  • Professor
  • Professor
  • User avatar
  • Posts: 854

Post 3+ Months Ago

So I have 2 tables in my database (actually more than that, but these 2 are relevant):

PHP Code: [ Select ]
Player Table
_____________________________________________
| playerid | pla_lname | pla_fname | teamid |
---------------------------------------------
| 1        | bob       | goober    | 2      |
---------------------------------------------
 
 
Team Table
________________________
| teamid   | team_name |
------------------------
| 1        | tigers    |
------------------------
| 2        | bears     |
------------------------
 
  1. Player Table
  2. _____________________________________________
  3. | playerid | pla_lname | pla_fname | teamid |
  4. ---------------------------------------------
  5. | 1        | bob       | goober    | 2      |
  6. ---------------------------------------------
  7.  
  8.  
  9. Team Table
  10. ________________________
  11. | teamid   | team_name |
  12. ------------------------
  13. | 1        | tigers    |
  14. ------------------------
  15. | 2        | bears     |
  16. ------------------------
  17.  


Let's pretend that there are a larger set of columns in each table and far more entries in each table. I have a select query that gets all entries in the player table. My result looks like this:
PHP Code: [ Select ]
_____________________________________________
| playerid | pla_lname | pla_fname | teamid |
---------------------------------------------
| 1        | bob       | goober    | 2      |
---------------------------------------------
 
  1. _____________________________________________
  2. | playerid | pla_lname | pla_fname | teamid |
  3. ---------------------------------------------
  4. | 1        | bob       | goober    | 2      |
  5. ---------------------------------------------
  6.  


However, I'd prefer it to be this (pay attention to the team portion):
PHP Code: [ Select ]
_____________________________________________
| playerid | pla_lname | pla_fname |teamname|
---------------------------------------------
| 1        | bob       | goober    | bears  |
---------------------------------------------
 
  1. _____________________________________________
  2. | playerid | pla_lname | pla_fname |teamname|
  3. ---------------------------------------------
  4. | 1        | bob       | goober    | bears  |
  5. ---------------------------------------------
  6.  


I've googled for a while now and I'm pretty sure I need to use a join? I checked out joins...have absolutely NO idea how to use them to do what I would like to do. Any takers? What should my query look like?
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • this213
  • Guru
  • Guru
  • User avatar
  • Posts: 1260
  • Loc: ./

Post 3+ Months Ago

There are a few ways you can do it. If you "must" use a JOIN:
Code: [ Select ]
SELECT a.playerid,a.pla_lname,pla_fname,b.teamname FROM players a INNER JOIN teams b ON a.teamid=b.teamid


Considering that every player would have a team assigned (which may not be the case), you could also do:
Code: [ Select ]
SELECT a.playerid,a.pla_lname,pla_fname,b.teamname FROM players a,teams b WHERE a.teamid=b.teamid

However, this last will NOT return any rows that don't have an association in the "teams" table - you may find this useful as well though (the JOIN statement will grab rows that aren't associated in the teams table).

Also, if your database is heavily loaded, you may want to pull the records you want into arrays and then do your matching in the code. I'll usually do this if I'm grabbing all records associated to a given set just to transfer load from the db to the script.
  • mindfullsilence
  • Professor
  • Professor
  • User avatar
  • Posts: 854

Post 3+ Months Ago

Awesome, thanks 213. Think you could break the first one down for me so I understand it a bit better? The second example is pretty self explanatory, I think I get that one just fine. Reasearching (googleing) JOIN comes up with about a bajillion blogs that do nothing more then copy-paste the MySQL documentation, which I don't understand. I need a different explanation. If you have the time I'd greatly appreciate the effort.
  • this213
  • Guru
  • Guru
  • User avatar
  • Posts: 1260
  • Loc: ./

Post 3+ Months Ago

http://dev.mysql.com/doc/refman/5.0/en/join.html
This page describes JOIN statements better than I could, but perhaps I'm just used to reading technical documentation.

JOIN simply does as it says, run this query (SELECT a.playerid,a.pla_lname,pla_fname,b.teamname), select from this table (FROM players a), also select from this table (teams b) where the first associates to the second in this manner (ON a.teamid=b.teamid).

As to the different types of JOINs (LEFT, RIGHT, CROSS, NATURAL, INNER, OUTER) this all depends on what records you want returned if there aren't matching rows in the tables. Rather than write a 3 page essay on how these work, I'll invite you to create a couple tables with rows that don't associate as well as rows that do and play with the query syntax.

In my own code, I hardly ever use JOIN (there are times, but it's rare). 9 times out of 10, I need 1 of 2 things: A specific set from all tables involved that are associated properly, so the second example works as expected and in a stable manner. Using a JOIN in code such as this may result in rows being returned that are not associated to the primary table and that means you're pulling data that you don't need, which in turn could end up messing up the data structures in your code (such as associative arrays holding extra elements which in turn could supply a ghost record set with no primary id). Or I need to perform some sort of work on every row in a table and those cases, depending on the size of the table, I'll either just pull the whole thing in one go or I'll iterate over each record in one place in my code. Either way, there's no need for a JOIN.

I will state though, if you haven't noticed, I'm something of a code nazi. This doesn't just apply to how my code looks, but to how it operates. All of my database tables have devices built in for association, I tend to just naturally write normalized tables without really thinking about it. What that means is if there are rows in a given table that aren't somehow associated to another, it's because there is no relation between the two. If two table do have associations and there are rows in one that aren't associated to the other, it means someone has been messing with my code or talking to my db from another code base and that data is either entered wrong or outright doesn't belong there. I get paid double my hourly rate to fix other people's trash, so my employers usually won't let anyone else near my work. In either case, if there is data in my databases that doesn't belong and isn't associated right, my own code won't pull those records anyway.
  • mindfullsilence
  • Professor
  • Professor
  • User avatar
  • Posts: 854

Post 3+ Months Ago

this213 wrote:
JOIN simply does as it says, run this query (SELECT a.playerid,a.pla_lname,pla_fname,b.teamname), select from this table (FROM players a), also select from this table (teams b) where the first associates to the second in this manner (ON a.teamid=b.teamid).


You have no idea how helpful that statement was - try finding anything as concise as that on the net, not an easy task. Thanks a million.

this213 wrote:
In my own code, I hardly ever use JOIN (there are times, but it's rare). 9 times out of 10, I need 1 of 2 things: A specific set from all tables involved that are associated properly, so the second example works as expected and in a stable manner. Using a JOIN in code such as this may result in rows being returned that are not associated to the primary table and that means you're pulling data that you don't need, which in turn could end up messing up the data structures in your code (such as associative arrays holding extra elements which in turn could supply a ghost record set with no primary id). Or I need to perform some sort of work on every row in a table and those cases, depending on the size of the table, I'll either just pull the whole thing in one go or I'll iterate over each record in one place in my code. Either way, there's no need for a JOIN.

I will state though, if you haven't noticed, I'm something of a code nazi. This doesn't just apply to how my code looks, but to how it operates. All of my database tables have devices built in for association, I tend to just naturally write normalized tables without really thinking about it. What that means is if there are rows in a given table that aren't somehow associated to another, it's because there is no relation between the two. If two table do have associations and there are rows in one that aren't associated to the other, it means someone has been messing with my code or talking to my db from another code base and that data is either entered wrong or outright doesn't belong there. I get paid double my hourly rate to fix other people's trash, so my employers usually won't let anyone else near my work. In either case, if there is data in my databases that doesn't belong and isn't associated right, my own code won't pull those records anyway.


Sounds a lot like how my instructor wrote his database for this project. Every table is associated in some manner to another table and all tables are strung together via primary/foreign keys. I still have a bit of trouble understanding primary and foreign keys, or databases in general but am slowly getting it.

Sounds like you've gained a significant amount of respect at your workplace through your workflow - we'll need to chat about that sometime and see if you can't teach me a thing or two, I'd be really interested in hearing how you structure everything.
  • mindfullsilence
  • Professor
  • Professor
  • User avatar
  • Posts: 854

Post 3+ Months Ago

Okay I have one for you that I can't seem to figure out.
I have a table called new_player_temp that stores information on a new registrant. It holds all of the same information as my player table except it has no playerid or teamid column.
It's meant to hold the new registrants until the administrator logs in and assigns them a team, at which point the information is deleted from the new_player_temp table and added to the player table.
In order to do this, I need to get all columns on a particular name in the new_player_temp table, as well as all teamid's and team_names from the team table. This way I can just create a dropdown menu based on the team names in the team table.
This one eludes me because I'm getting only one row from my new_player_temp table and all rows from my team table, and they don't have any correlation to each other. I think I need a JOIN in this instance...and after researching the past couple days I think I need either a RIGHT JOIN or LEFT JOIN. I was able to get information from both tables but it ended up being what I understand to be a "CROSS JOIN" and there was 6 repeating rows with the same information from the new_player_temp row, combined with an extra column for each team_name from the team column.

Anyway, to illustrate once more, here are my tables:
PHP Code: [ Select ]
team Table
________________________
| teamid   | team_name |
------------------------
| 1        | tigers    |
------------------------
| 2        | bears     |
------------------------
 
new_player_temp Table
_________________________
| pla_lname | pla_fname |
-------------------------
| bob       | goober    |
-------------------------
 
  1. team Table
  2. ________________________
  3. | teamid   | team_name |
  4. ------------------------
  5. | 1        | tigers    |
  6. ------------------------
  7. | 2        | bears     |
  8. ------------------------
  9.  
  10. new_player_temp Table
  11. _________________________
  12. | pla_lname | pla_fname |
  13. -------------------------
  14. | bob       | goober    |
  15. -------------------------
  16.  


And my desired result (not really sure what this is supposed to look like with varying number of rows):
PHP Code: [ Select ]
________________________________________________
| pla_lname | pla_fname | teamid   | team_name |
------------------------------------------------
| bob       | goober    | 1        | tigers    |
------------------------------------------------
|           |           | 2        | bears     |
------------------------------------------------
 
  1. ________________________________________________
  2. | pla_lname | pla_fname | teamid   | team_name |
  3. ------------------------------------------------
  4. | bob       | goober    | 1        | tigers    |
  5. ------------------------------------------------
  6. |           |           | 2        | bears     |
  7. ------------------------------------------------
  8.  

That's about as good as I can visualize how the result would look. Can't seem to come across any examples of this from google. If it does come out like this...it's going to be a PHP parsing nightmare but at least I can figure it out from that point.
  • this213
  • Guru
  • Guru
  • User avatar
  • Posts: 1260
  • Loc: ./

Post 3+ Months Ago

ok, I have a couple question for you:

1. Why even have a separate "temp" table instead of just setting a status field in the players table - or even better, just make their teamid = 0? Wouldn't this make life easier?

2. It seems to me you'd end up with a list of non-assigned players, with their own list of available teams. Considering this, why not just pull the team data and store it into an array that you then reuse for each drop down. This is exactly the sort of thing I was talking about when I stated that a lot of times you just want all the data in a given table anyway. If you do something like:
Code: [ Select ]
$teams = array();
$result = mysql_query('SELECT * FROM teams');
while( $row = mysql_fetch_assoc($result) ){
    $team_id = $row['teamid'];
    $teams[$team_id] = $row;
}

$players = array();
$qual = "WHERE teamid='0'";
$result = mysql_query('SELECT * FROM players $qual');
while( $row = mysql_fetch_assoc($result) ){
    $player_id = $row['playerid'];
    $players[$player_id] = $row;
}
  1. $teams = array();
  2. $result = mysql_query('SELECT * FROM teams');
  3. while( $row = mysql_fetch_assoc($result) ){
  4.     $team_id = $row['teamid'];
  5.     $teams[$team_id] = $row;
  6. }
  7. $players = array();
  8. $qual = "WHERE teamid='0'";
  9. $result = mysql_query('SELECT * FROM players $qual');
  10. while( $row = mysql_fetch_assoc($result) ){
  11.     $player_id = $row['playerid'];
  12.     $players[$player_id] = $row;
  13. }

Now teams and players are in their own arrays. If you just want to grab player data one at a time, you could do that too, but you only have to grab the team data once, and that makes your code more efficient. There's no parsing nightmare here and pretty much anyone who can read PHP can see exactly what's going on. When you assign a given player to a team, you just change their team id. You could also make a separate "status" field in that table to show whether they'd been assigned, but there's really no need to do so. print_r() these two arrays and you can see exactly what you need to do to get to each element, it'll be something like:
Code: [ Select ]
foreach( $players as $player_id -> $player_data ){
    $player_team_id = $player_data['teamid'];
    $team_name = $teams[$player_team_id]['team_name'];
}
  1. foreach( $players as $player_id -> $player_data ){
  2.     $player_team_id = $player_data['teamid'];
  3.     $team_name = $teams[$player_team_id]['team_name'];
  4. }
  • mindfullsilence
  • Professor
  • Professor
  • User avatar
  • Posts: 854

Post 3+ Months Ago

Totally would do it that way, and even questioned my instructor about his method and the reasoning behind it about a week ago as to why we needed a seperate table for new registers. He didn't have a clear answer for me and unfortunately this is how he wants the database setup. I would much rather just add the players into the players table rather than having the separate table and whenever I needed to assign players, I could just do a simple query to find all players with teamid = null.

However, I'm guessing I can do 2 queries to the database set to separate variables to pull from each table, definitely will cut down on my confusion with doing it all in one query. Hadn't thought of that.

Post Information

  • Total Posts in this topic: 8 posts
  • Users browsing this forum: No registered users and 143 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.