Selecting Records

[EverettG]

I have a table [team] that contains team records - team_id primary key, team_name etc. I have another table [schedule] that contains game schedules - schedule_id primary key, visitor_id, hometeam_id, etc. Each row of the schedule table represents one scheduled game between two teams. visitor_id and hometeam_id in the schedule table correspond to team_id in team table. I am trying to construct a SELECT statement so that the output contains the team_name for both the visitor_id and hometeam_id. Is it possible to reference the same field contents from two separate fields in the same tablein the same Select statement. Have tried many variations of Selects, joins, etc with no success. Really stumping me. Currently reading the book and find it very thorough and a great learning tool. Thank you.

 

Everett

[HartleySan]

Sorry for the incomplete answer at this juncture, but I am in a bit of a hurry. I am almost sure that you'll want to use a join between the two tables though, although I'm rather weak at joins myself, and would require a bit to come up with an appropriate one.

 

If someone else answers the question before me, than great. Otherwise, I'll try and answer it later.

[Jonathon]

I'd agree wth HartleySan, I am unfortunately only with my phone today which drastically reduces my output I'm afraid. I also have to really work hard to get joins right. But if you post you tables and columns layout it would be a bit easier for us to see the big picture. I think you're close. What query are you trying?

[HartleySan]

Without seeing the entire table structure, I can't guarantee the following will work, especially considering my weakness with JOIN statements. Anyway, please try something like the following:

 

SELECT schedules.schedule_id, team.team_name FROM schedules INNER JOIN team WHERE schedules.visitor_id=team.team_id OR schedules.hometeam_id=team.team_id

 

My rationale for this query is that you'd want to display the results as follows:

 

_____Visitors_____Game____Hometeam_____

visitors-team-name...1...hometeam-team-name

visitors-team-name...2...hometeam-team-name

visitors-team-name...3...hometeam-team-name

visitors-team-name...4...hometeam-team-name

visitors-team-name...5...hometeam-team-name

 

 

If that's the case, then the above query should work. I mean, test the query out using the SQL tab in phpMyAdmin, and see if the results are satisfactory. If they are, great, if not, let us know. Anyway, there are a lot of different ways to write JOIN statements, but I went with the most verbose one I could think of, since it's the easiest to understand.

 

Also, thanks for your query (pun intended) about JOIN statements. It really forced me to sit down and actually try to write it out, which is always good practice.

[EverettG]

First, thank you all for your replys. It is nice to know that there are people who actually want to help. Also, I want to thank HartleySan not only for his reply, but for his nice comment about asking about a join query which gave him some practice in an area I know most of us struggle with.

 

Back to the problem: Below are the two table structures. The team table contains the Name column that I am trying to return in the Select. It needs to be returned twice in the same Select, once for the home team and again for the visiting team. I am trying to return a row that is representative of each scheduled game. The schedule table contains the columns VisitorId and HomeId which relate to the ID column in team. The output I am trying to achieve is something like this except with team.Name replacing the visitorid and homeid columns. I would like to find a way to replace visitorid and homeid in the Select with team.Name as Visitor, team.Name as HomeTeam or something like that so that both columns contain the appropriate team name. By the way, this doesn't work either, it returns the team name for the home team in both columns[based on the ON clause]. I also tried reversing the table names in the INNER JOIN clause, same result.

This result was achieved through the query:

 

select date, time, visitorid, homeid, stadium
from schedule inner join team
on team.id = schedule.homeid
where season='2011' and week='1';

 

+------------+----------+-----------+--------+-------------------------------+
| date       | time     | visitorid | homeid | stadium                       |
+------------+----------+-----------+--------+-------------------------------+
| 2011-09-08 | 20:30:00 |        20 |     12 | Lambeau Field                 |
| 2011-09-11 | 13:00:00 |         2 |      6 | Soldier Field                 |
| 2011-09-11 | 13:00:00 |         7 |      8 | Cleveland Browns Stadium      |
| 2011-09-11 | 13:00:00 |         4 |     16 | Arrowhead Stadium             |
| 2011-09-11 | 13:00:00 |        24 |     29 | Edward Jones Dome             |
| 2011-09-11 | 13:00:00 |        11 |     30 | Raymond James Stadium         |
| 2011-09-11 | 13:00:00 |        31 |     15 | EverBank Field                |
| 2011-09-11 | 13:00:00 |        25 |      3 | M&T Bank Stadium              |
| 2011-09-11 | 13:00:00 |        14 |     13 | Reliant Stadium               |
| 2011-09-11 | 16:15:00 |        19 |     32 | FedEx Field                   |
| 2011-09-11 | 16:15:00 |        28 |     27 | Candlestick Park              |
| 2011-09-11 | 16:15:00 |        18 |     26 | Qaulcomm Stadium              |
| 2011-09-11 | 16:15:00 |         5 |      1 | University of Phoenix Stadium |
| 2011-09-11 | 20:20:00 |         9 |     22 | New Meadowlands Stadium       |
| 2011-09-12 | 19:00:00 |        19 |     17 | Sun Life Stadium              |
| 2011-09-12 | 22:15:00 |        23 |     10 | Invesco Fiedl at Mile High    |
+------------+----------+-----------+--------+-------------------------------+

 

I have even tried different variations on a UNION with no success. I am continuing to try every combination of query I can think of to achieve my desired output, so please let me know if you come up with anything new.

 

HartleySan, here is the output from your submitted query. I want to thank you for your effort. It did return the names of both teams scheduled to play against each other, although in separate records with linking schedule.id. This did give me a new way to look at how to grab data in a way I had not thought of before, and for that I thank you. It was interesting to note the order in which the records were returned. The order was: for each set of schedule id teams, the first row was the lower team.id. It unfortunately doesn't tell me which team is the home team and which team is the visitor. As an example in the two rows identified by ID = 1, Green Bay is the home team [first row of ID = 1 rows], where in the two rows identified by ID = 2, Chicago is the home team [second row of ID = 2 rows]. Gave me more insight into how selects/joins return data though.

 

mysql> select schedule.ID, team.Name from schedule
   -> inner join team
   -> where visitorid = team.id or homeid = team.id;
+----+---------------+
| ID | Name          |
+----+---------------+
|  1 | Green Bay     |
|  1 | New Orleans   |
|  2 | Atlanta       |
|  2 | Chicago       |
|  3 | Cincinnati    |
|  3 | Cleveland     |
|  4 | Buffalo       |
|  4 | Kansas City   |
|  5 | Philadelphia  |
|  5 | St. Louis     |
|  6 | Detroit       |
|  6 | Tampa Bay     |
|  7 | Jacksonville  |
|  7 | Tennessee     |
|  8 | Baltimore     |
|  8 | Pittsburgh    |
|  9 | Houston       |
|  9 | Indianapolis  |
| 10 | New England   |
| 10 | Washington    |
| 11 | San Francisco |
| 11 | Seattle       |
| 12 | Minnesota     |
| 12 | San Diego     |
| 13 | Arizona       |
| 13 | Carolina      |
| 14 | Dallas        |
| 14 | New York      |
| 15 | Miami         |
| 15 | New England   |
| 16 | Denver        |
| 16 | Oakland       |
+----+---------------+
32 rows in set (0.19 sec)

 

Again, I want to thank you all for your efforts in helping me.

 

Everett

 

Table descriptions:

mysql> describe team;
+------------+-----------------------+------+-----+---------+----------------+
| Field      | Type                  | Null | Key | Default | Extra          |
+------------+-----------------------+------+-----+---------+----------------+
| ID         | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| Name       | varchar(30)           | NO   |     | NULL    |                |
| MascotName | varchar(30)           | NO   |     | NULL    |                |
| Conference | varchar(30)           | NO   |     | NULL    |                |
| Division   | varchar(30)           | NO   |     | NULL    |                |
| Stadium    | varchar(40)           | NO   |     | NULL    |                |
| TimeZone   | varchar(30)           | NO   |     | NULL    |                |
+------------+-----------------------+------+-----+---------+----------------+
7 rows in set (0.08 sec)

 

mysql> describe schedule;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| ID           | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| Season       | varchar(4)            | NO   |     | NULL    |                |
| Week         | varchar(2)            | NO   |     | NULL    |                |
| Date         | date                  | NO   |     | NULL    |                |
| Time         | time                  | NO   |     | NULL    |                |
| VisitorID    | mediumint(8) unsigned | NO   | MUL | NULL    |                |
| HomeID       | mediumint(8) unsigned | NO   | MUL | NULL    |                |
| VisitorScore | tinyint(2) unsigned   | NO   |     | NULL    |                |
| HomeScore    | tinyint(2) unsigned   | NO   |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+
9 rows in set (0.34 sec)

[HartleySan]

It's late, and headed to bed now, but thank you for the compliments and the detailed post. I will look into it tomorrow when I have more time. I think we are close to what you are looking for, so don't fret.

[Matthaus]

I would imagine the easiest way would actually be to do a three table join and join against teams twice.

Something like the following would work (probably). With this join the first id is joined against the first teams table and the second id is joined against the second teams table. The key here is to alias both tables so that mysql can tell that you're joining the same table twice.

 

Hope this helps.

 

select date, time, visitorid, t2.name as VisitorTeamName, homeid, t1.name as HomeTeamName, stadium
from schedule 
inner join team as t1 
   	on t1.id = schedule.homeid   
   inner join team as t2
   	on t2.id = schedule.vistorid
where season='2011' and week='1';

[EverettG]

Thank you Matthaus for your suggestion. Here are the results from your query:

mysql> select date, time, visitorid, t2.name as VisitorTeamName, homeid, t1.name as HomeTeamName, stadium
   -> from schedule
   ->         inner join team as t1
   ->         on t1.id = schedule.homeid
   ->     inner join team as t2
   ->         on t2.id = schedule.vistorid
   -> where season='2011' and week='1';
ERROR 1052 (23000): Column 'stadium' in field list is ambiguous

 

I changed it to the following with this result:

mysql> select date, time, visitorid, t2.name as VisitorTeamName, homeid, t1.name as HomeTeamName, t1.stadium
   -> from schedule
   ->         inner join team as t1
   ->         on t1.id = schedule.homeid
   ->     inner join team as t2
   ->         on t2.id = schedule.vistorid
   -> where season='2011' and week='1';
ERROR 1054 (42S22): Unknown column 'schedule.vistorid' in 'on clause'

 

I am working on the second query to try to get it to work. This may be the right path to success. Thank you.

 

Everett

[EverettG]

I have figured out a solution! Matthaus got me thinking in the right direction with his suggestion of multiple joins. Thank you again. I messed around with variations of his suggestion using inner joins with no success [see prior post]. Just for the heck of it I tried the same format of multiple joins but used left joins. It worked!? I am not quite sure why the left joins worked and the inner joins didn't though. I will need to do some more investigation to figure that out. If anyone has any theories as to why, please let me know so that I can really understand it. I will post my theory when I come up with one. Hope this helps others who are confronted by this same issue. Also, this brain teaser may help still others to understand joins a little more. I know it did for me. I will also be tweaking this query to see if any other variations also produce the desired output.

 

I want to again thank everyone who helped me in solving this query. Hope I can be as much help to others on this board with future questions.

 

Everett

 

Here is the query that worked along with the output [just what I was looking for]. Note - the alias t2 is required with stadium or the error "Column 'stadium' in field list is ambiguous" is produced and to also return the correct stadium [hometeam not visitor]:

 

mysql> select date, time, t2.stadium, concat(t1.name, ' ', t1.mascotname) as visitor,
   -> concat(t2.name, ' ', t2.mascotname) as hometeam
   -> from schedule
   -> left join team as t1
   -> on t1.id = visitorid
   -> left join team as t2
   -> on t2.id = homeid;
   -> where season = '2011' and week ='1';
+------------+----------+-------------------------------+----------------------+----------------------+
| date       | time     | stadium                       | visitor              | hometeam             |
+------------+----------+-------------------------------+----------------------+----------------------+
| 2011-09-08 | 20:30:00 | Lambeau Field                 | New Orleans Saints   | Green Bay Packers    |
| 2011-09-11 | 13:00:00 | Soldier Field                 | Atlanta Falcons      | Chicago Bears        |
| 2011-09-11 | 13:00:00 | Cleveland Browns Stadium      | Cincinnati Bengals   | Cleveland Browns     |
| 2011-09-11 | 13:00:00 | Arrowhead Stadium             | Buffalo Bills        | Kansas City Chiefs   |
| 2011-09-11 | 13:00:00 | Edward Jones Dome             | Philadelphia Eagles  | St. Louis Rams       |
| 2011-09-11 | 13:00:00 | Raymond James Stadium         | Detroit Lions        | Tampa Bay Buccaneers |
| 2011-09-11 | 13:00:00 | EverBank Field                | Tennessee Titans     | Jacksonville Jaguars |
| 2011-09-11 | 13:00:00 | M&T Bank Stadium              | Pittsburgh Steelers  | Baltimore Ravens     |
| 2011-09-11 | 13:00:00 | Reliant Stadium               | Indianapolis Colts   | Houston Texans       |
| 2011-09-11 | 16:15:00 | FedEx Field                   | New York Giants      | Washington Redskins  |
| 2011-09-11 | 16:15:00 | Candlestick Park              | Seattle Seahawks     | San Francisco 49ers  |
| 2011-09-11 | 16:15:00 | Qaulcomm Stadium              | Minnesota Vikings    | San Diego Chargers   |
| 2011-09-11 | 16:15:00 | University of Phoenix Stadium | Carolina Panthers    | Arizona Cardinals    |
| 2011-09-11 | 20:20:00 | New Meadowlands Stadium       | Dallas Cowboys       | New York Jets        |
| 2011-09-12 | 19:00:00 | Sun Life Stadium              | New England Patriots | Miami Dolphins       |
| 2011-09-12 | 22:15:00 | Invesco Fiedl at Mile High    | Oakland Raiders      | Denver Broncos       |
+------------+----------+-------------------------------+----------------------+----------------------+
16 rows in set (0.00 sec)

[HartleySan]

Granted, the database I created to try this out is much simpler than yours, but the concepts are the same, and I got an INNER JOIN to work fine using the following:

 

SELECT schedules.schedule_id, t1.team_name, t2.team_name
FROM schedules
INNER JOIN team AS t1 ON t1.team_id = schedules.visitor_id
INNER JOIN team AS t2 ON t2.team_id = schedules.hometeam_id

 

Matthaus definitely hit the nail on the head in regards to using multiple joins on the same table to return all the desired results in one query. Thanks, Matthaus. I will definitely remember that.

 

EverettG, if you don't mind, there are a couple of things I noticed with your database design that you might want to consider for better normalizing everything:

1) the date and time fields could probably be combined into one DATETIME or TIMESTAMP field, which could be parsed on the MySQL side as necessary.

2) You should probably have a separate table for stadiums. And then link the stadium ID back to the teams tables, so that you know what the home fields are for each team.

3) You misspelled "Invesco Field".

4) You could have an algorithm on the PHP side to figure out the season and the week, so that you can grab the appropriate data from the database using the DATETIME/TIMESTAMP field.

 

Well, anyway, just some suggestions. I'm glad you solved your problem though, and I think we all learned something from this post, so thanks. Also, Matthaus, thanks again for your expert knowledge. That was very helpful.

[EverettG]

Thanks again HartleySan for your efforts in helping me not only come up with a query but for helping me understand joins better. I did take your query and adapt it to my tables and columns while keeping the intent of the query intact. Here is the query and results:

 

mysql> SELECT schedule.id, t1.name, t2.name
   -> FROM schedule
   -> INNER JOIN team AS t1 ON t1.id = schedule.visitorid
   -> INNER JOIN team AS t2 ON t2.id = schedule.homeid;
+----+--------------+---------------+
| id | name         | name          |
+----+--------------+---------------+
|  1 | New Orleans  | Green Bay     |
|  2 | Atlanta      | Chicago       |
|  3 | Cincinnati   | Cleveland     |
|  4 | Buffalo      | Kansas City   |
|  5 | Philadelphia | St. Louis     |
|  6 | Detroit      | Tampa Bay     |
|  7 | Tennessee    | Jacksonville  |
|  8 | Pittsburgh   | Baltimore     |
|  9 | Indianapolis | Houston       |
| 10 | New York     | Washington    |
| 11 | Seattle      | San Francisco |
| 12 | Minnesota    | San Diego     |
| 13 | Carolina     | Arizona       |
| 14 | Dallas       | New York      |
| 15 | New England  | Miami         |
| 16 | Oakland      | Denver        |
+----+--------------+---------------+
16 rows in set (0.20 sec)

 

As you can see it is what I was looking for in its' simplest form. I can use this to rebuild the entire query to return the other column data that I need. Thanks again. I also appreciate the comments on database design. They are all very good suggestions and I will incorporate them into my design. The one about stadiums is one I had already planned on making. I start out my database designs with few tables with lots of data to get me started, and then break things up over time. I do appreciate the suggestions though, keep them coming. It is always good when someone looks at code/designs from a totally objective perspective. I don't often get good feedback like that. I hope you will continue to monitor this board as I will be contributing more and more over time. It would be even better if somehow we could continue to exchange ideas and expertise outside of this board. Let me know if that is possible.

Again, thanks to all who helped me with this. I am still looking at the LEFT JOIN and will dissect the INNER JOIN provided by HartleySan. I will post any insights I discover as I try to understand both querys. If anyone has any further comments/suggestions please post them as I know I can only understand the concepts more with more input.

Thanks.

 

Everett

[EverettG]

This is mainly for Matthaus. As I was looking over HartleySan's query that was sucessful, I compared it to your original query. They were basically the same and I was confused as to why I was getting an error. Then I discovered that I spelled visitorid incorrectly [vistorid]. I apologize for this oversight. As it turns out, your INNER JOIN query did work fine. If anything good came out of my mistake, it led me to discover another solution using LEFT JOINs. It's funny how things happen. Thanks for your help.

 

Everett

[HartleySan]

Yeah, I was just trying to boil the query down to the essentials, so that it can be built on from there. Also, I completely understand what you mean by starting out with things in one table, and then breaking them up at a later time.

 

Also, if I could figure out how to list my email address in my profile for people to contact me, I would do that, but I cannot figure out how to do that!

 

Larry, how the heck do you add your email address to your profile for people to see?

[Matthaus]

This is mainly for Matthaus. As I was looking over HartleySan's query that was sucessful, I compared it to your original query. They were basically the same and I was confused as to why I was getting an error. Then I discovered that I spelled visitorid incorrectly [vistorid]. I apologize for this oversight. As it turns out, your INNER JOIN query did work fine. If anything good came out of my mistake, it led me to discover another solution using LEFT JOINs. It's funny how things happen. Thanks for your help.

 

Everett

 

It's quite possible you spelled visitorid incorrectly because I did it first (yay typos!). Glad you got it working.

 

-matthaus

[zabberwan]

how the heck do you add your email address to your profile for people to see?

 

To do that, go to your " My Settings" - scroll arrow next to your user name - and go to "Notification Options" and remove the check from "Hide my email from other members"

 

[HartleySan]

Thanks for that info.