JOIN help

[lindsaycb]

I have 2 database tables; athRosters with athletic players and their bios (height, weight, likes, hobbies, etc), and the athPast table lists players that no longer play with my community college but have moved on to Universities. They both have the column of ‘playerName’ in common. Some players are listed in both tables. Some are only listed in the athPast table.

I have figured out, using JOIN, how to show the data of those that are listed in both tables AND include a link to the bio page.

But now my problem is showing the rest of them; those in athPast and NOT show the ones that are listed in both (because then I get them twice).


SELECT athPast.yearsPlayed,
athPast.playerName,
athRosters.playerName,
athPast.conference,
athPast.college,
athRosters.playerCollege,
athRosters.rostSport
FROM athRosters LEFT JOIN athPast
ON athRosters.playerName = athPast.playerName
WHERE athRosters.playerCollege = ''
AND athRosters.rostSport = '".$_GET['gp']."'
ORDER BY athPast.yearsPlayed DESC";

I thought that if I used the athRosters.playerCollege which lists what college they transferred too on their bio page as a variable to filter then it would work but it doesn’t.

If this is confusing, just let me know what else I need to provide.

[boogyman]

its generally not advised to use a text field to compare between fields, because it is very likely that one day you can have a "second player" with the same name.

Joe Smith
Bob Johnson

both names above could very well be names of players because those are common first / last names. What I would suggest for this is to create an auto-incrementing "player_id" for the current player list, than using a foreign key in the past player list that will connect the two.

Now as for your question, I typically create and run a second query and check the unique id "player id" in this case to use my previous example and if there is a match between the two, don't include the player, then just combine the two array sets as appropriate

[lindsaycb]

I took your suggestion and tried adding in a Foreign Key once I looked up what it was but it doesn't work. So it looks like I'm going to have to stick with the names. Plus, sometimes a player will play in multiple sports so they have multiple entries in the Current Player Bio database.

And since you used the player_id as an example in your solution, I don't know how to incorporate yours. I'll just keep playing with it. I may have to end up just including all the players, even if they repeat. I've been working on this for 3 days now. I think it's time to admit defeat.

Thanks for replying!

[Twey]

'Doesn't work' isn't really an accurate description of the problem. If it doesn't work, chances are it's because you applied it wrongly. If you were to show the errors that resulted and your existing code (including the SQL for your tables), we'd probably be able to apply it properly for you.

A little off-topic: I didn't think Israel had community colleges? What's the system, then?

[lindsaycb]

I live in Illinois, in the United States so I'm not sure where you got Israel from.

I don't think I know enough of the Foreign Key to make it work and these are tables that when I got here was already here, so I don't know how they were set up.

What I mean by "doesn't work" is that it ended up not showing any of the names that appear in both tables. So obviously the foreign key didn't work as in again, I don't know that much about it.

I just went ahead and showed all the names. If someone says something, I'll look back into it then for now, it'll have to do.

I'm running into another problem now that it's not filtering the names based on sport (there is one woman who was in 2 sports). So I'm going to concentrate on that problem.
thanks for the reply.

[Twey]

Ah, your location says 'IL', which is the international country code for Israel. Pardon the confusion.

You can get an SQL dump of the tables using the administrative tools for your database: if you're using MySQL, that's mysqldump. If for some reason you can't do that, running 'explain <tablename>' or using phpMyAdmin to get an overview of the tables will suffice, too.