Joining 3 tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
splashz
Forum Newbie
Posts: 2
Joined: Wed Aug 04, 2010 11:29 am

Joining 3 tables

Post by splashz »

I have 3 tables, league, game, and line. For the sake of this post, lets say the tables are structured as follows.
league
idleague, leaguename, misc league info

game
idgame, idleague, misc game info

line
timestamp, idgame, misc line info

I have a script that inserts data into the database every minute or so and uses the timestamp field. I want a select statement that will get the rows from the most recent timestamp, and trace back to the league table and return the leaguename of the leagues that were updated. Does that make sense? It sounds confusing even as I write it.

* Forgot to note this is MySQL
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Joining 3 tables

Post by AbraCadaver »

Not tested:

[text]SELECT leaguename FROM league
JOIN game ON league.idleague = game.idleague
JOIN line ON game.idgame = line.idgame
ORDER BY line.timestamp DESC
LIMIT 1[/text]
Use * or a list of columns instead of leaguename if you want all or specific columns.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply