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

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

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)

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

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)

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