SQL Query Help (2 columns used as index into another table)

Posted: Mon Dec 04, 2006 2:10 pm

This is over my head. You don't have to solve this for me, just perhaps give me a pointer or two to go looking.

I have a table that represents "matches" (date, time, home team id , away team id) and a table for teams (name, color, etc). The home team id and away team id in the matches table are indices into the teams table.

I was writing a block to display upcoming matches. I wanted to write one select statement that would return the date, time, home team name, home team color, away team name, away team color. But since my matches table has two colums that index into the teams table I was kind of stumped.

I solved it by breaking it down into two SQL queries. First I just did a select * on the teams table to get all that info. Then I ran my query on the matches table and used PHP code to combine the data. But I couldn't help but wonder if there was a way to do this with only 1 SQL query using some technique I'm not aware of.

If there was only 1 team ID in the matches table I could handle that (with a "WHERE matches.home_id = teams.team_id). But there are two and I couldn't get my mind wrapped around that.

Any ideas? Thanks!

Posted: Mon Dec 04, 2006 4:11 pm

There may be a SQL expert around who can figure out how to do this with a doublesecret_inner_outer_join or something and I'd be prepared to learn from them. But I don't see any immediate solution without a few SQL calls.

I can see where you'd really want two types of reports from this: a matches report and a teams report. The matches report would show what teams are in each match and the teams report would show, for a given team, what matches they have.

For the first, matches report, I think that what you'd do is read the matches (perhaps selected the date range you are interested in or at least sorting by date) table and stick it in a while loop. Then for each record in the loop you'd retrieve the home team id and away team id and look in the teams table for the color and name. You could retrieve both team records with on SQL call by doing an OR but then you'd have to test them in a while loop to see if you matched on the home team or away team id. So you might as well just do two SQL calls I think and just get the home team or away team id specifically. Stick them into variables and print a line in your rfeport when you have processed both records, then go onto the next match record.

For the teams report what you'd do is find all the team records and stuff them into a while loop. Then find the matches where a given team is either a home team or an away team. You'd have the name and color of the team you are processing at the time in the loop but you'd need to do a lookup on the other team. Stuff these all into variables and echo out one line for each match that you find.

I'll be interested to see if anyone comes up with better "magic" than this and thanks for the intriguing question.

Posted: Mon Dec 04, 2006 5:20 pm

Well as I said, I solved it with 2 calls. I built a "teams" array from one SELECT team_id, team_name, team_color FROM teams. The $teams array was indexed by team_id. Each element in this array was an array itself containing name and color.

Then the second select got the upcoming matches...something like SELECT date, time, home_id, away_id FROM matches WHERE date >= CURDATE().

I looped over that result set, and used my $teams array that I had previously built whenever I needed team information...e.g. $teams[$row['home_id']]['color']

I'm like you though, there must be some double secret join that I don't know about that could give me all this in one SELECT call. Smile

I'm going to go read up on some SQL statements that I don't know anything about...like UNION for example, and see what they do..

Posted: Mon Dec 04, 2006 6:12 pm

Well post the results back here. You are way ahead of me on this. When I was learning databases they didn't have SQL, and that sure dates me.

Posted: Mon Dec 04, 2006 6:58 pm

I tried playing with union, and it simply concatenates the rows from one select with the rows from another. So this sql:

Code:




SELECT m.match_date, m.match_time, m.match_home, m.match_away, t.team_name


FROM nuke_clan_matches AS m, nuke_clan_teams AS t


WHERE m.match_date >= CURDATE( )


AND m.match_home = t.team_id


UNION SELECT m.match_date, m.match_time, m.match_home, m.match_away, t.team_name


FROM nuke_clan_matches AS m, nuke_clan_teams AS t


WHERE m.match_date >= CURDATE( )


AND m.match_away = t.team_id

produced 2 rows for each match...one with the team_name home and the other the away team name.

That isn't quite what I wanted. I'll have to look into the crazy join syntax next.

registered · Posted: Tue Dec 05, 2006 6:42 am

Gremmie, question for you. Would my assumption always be 100% correct that your matches table ALWAYS has both home team id and away team id valued AND you have sufficient data integrity rules in place to ensure that these same id's will have "hits" in the teams table?

If this assumption is correct, you simply join twice to the teams table, using the home team id for one and away team id for the second. No fancy INNER/OUTER JOINS are necessary if the assumption is always true.

Posted: Tue Dec 05, 2006 9:54 am

Well up until recently I thought so...but that is beside the point. Let's say that the assumption is still correct...how do I join twice to the teams table? I'm struggling with that syntax. I think there is something in here to help me:

http://dev.mysql.com/doc/refman/4.1/en/join.html

but I haven't quite grokked it yet.

Posted: Tue Dec 05, 2006 8:04 pm

It might look something like this:

Code:




SELECT m.match_date, m.match_time, m.match_home, m.match_away, t1.team_name AS team_name_home, t2.team_name AS team_name_away 


FROM nuke_clan_matches AS m, nuke_clan_teams AS t1, nuke_clan_teams AS t2


WHERE m.match_date >= CURDATE( ) 


AND m.match_home = t1.team_id


AND m.match_away = t2.team_id

Then you could access the different names like this:

$row['team_name_away'] or $row['team_name_home']

Posted: Tue Dec 05, 2006 8:40 pm

Awesome...thanks! I will try this out.

Do you have a link to a good SQL tutorial?

Posted: Tue Dec 05, 2006 9:54 pm

Yup, worked great. Thanks for the tip. I did not know you could join from the same table twice. Makes sense now.

Posted: Wed Dec 06, 2006 6:05 am

You are welcome. Regarding a good tutorial, unfortunately, I am not aware of one. But, I also have not looked. Maybe others here can recommend something.

Posted: Wed Dec 06, 2006 10:15 am

Here's an excellent one http://www.ravenphpscripts.com/postt11992.html

Posted: Wed Dec 06, 2006 6:31 pm

ROFL, Guardian ... I haven't laughed that hard in years. A join on a post?

Posted: Wed Dec 06, 2006 7:34 pm

OMG, was that an Embarassed

or Brit humor coming out! That is just too good either way... ROTFL

Posted: Thu Dec 07, 2006 3:44 am

Just my dry Brit humour Wink