SELECT Statement Question

I received some great advice a couple of years ago when using this forum, and I have another (hopefully easy) question.

I have two tables:

Table 1: score_results with four fields: schoolID, team1_score, opponentID; team2_score

It includes the following records:

1001; 6; 2005; 4
2001; 8; 2010; 3
2005; 10; 2010; 5

Table 2: team_name with two fields: schoolID, schoolName

1001; Washington
2001; Taft
2005; Howard
2010; Lincoln

What I want for the output is:

Washington 6; Howard 4
Taft 8; Lincoln 3
Howard 10; Lincoln 5

I can’t figure out how to use the team_name table to lookup the names of both schools. I can get one using a LEFT JOIN, but not the second name.

Any help would be greatly appreciated.


For the quickest correct answer, post your database schema

Is this what you would like for me to provide? The actual tables are quite large so I have pulled out only the relevant fields.

Table: score_results

Table: team_name
schoolID (primary field)

I meant a file I could import to recreate your database to test on it. A empty sql file is not going to be that big. I just need the create table info.

team1.schoolName as team1,
score.team1_score as score1,
team2.schoolName as team2,
score.team2_score as score2

FROM score_results as score

LEFT JOIN team_name as team1 ON ( = score.schoolID)
LEFT JOIN team_name as team2 ON ( = score.opponentID)[/code]


TEAM1 SCORE1 TEAM2 SCORE2 Washington 6 Howard 4 Taft 8 Lincoln 3 Howard 10 Lincoln 5


JimL, +1 for taking the time to do do the extra work to recreate the database the poster could have supplied. Had he provided the schema we may have been able to point out any potential problems with his database design which is one of the reasons I asked for it in the first place.

The one thing though, that query will return all rows. He didn’t specify if it was just that format or those three records specifically. He did say it was a “Large” table, so as is he could get back thousands of results depending on the size of the database.

What I want for the output is:

Washington 6; Howard 4
Taft 8; Lincoln 3
Howard 10; Lincoln 5

If you were wanting those specific records, you would add this to your query:

WHERE schoolID= 1001 OR schoolID= 2001 OR schoolID= 2005


Wow. This is awesome. I don’t understand how this works (yet), but it worked.

I can now use this code in numerous places and it will be a huge help for me.

Thank you very much for your help.



Thank you for this code as well. I’m sorry I didn’t exactly understand what you were asking for. Could you please direct me to an example so that I can use the correct approach to request help?

What you are providing is very, very beneficial.


Open up PHPmyadmin (or command line) and export the tables, then you will get the schemas for building the tables and populating them. The same as you could see in the left column in the sqlfiddle example. It makes it easier for us because we have to hand type that code while you can do the same in a couple of clicks :slight_smile:

The trick in my query is naming the two joins so that I van use them to reflect different teams

Below is what I was asking for. Like Jim said, it is something you could do in a couple clicks if you knew how as opposed to us hand writing it out. We spend a lot of our time on this board helping people, so the easier you make it for us to help you the quicker and more likely you will get a response. At the time you posted, I didnt have the time, nor the desire to type it all out. A brutha has to make a living too. We dont get paid on this board.

If you are interested in learning as opposed to just getting the fix you need I will post some additional querys you may find useful to what you are doing. Things like Get all the results where Washington won, or everyone they lost against. Their average for the season or a quarter (Gotta see if they are improving for those Vegas bets right?) Stats between XXX year and XXX year, Ranking by team, etc…

I can also introduce you to subquerys. If there is specific info you would you would like to see let me know.

If your going to be working with Databases a lot I would I would HIGHLY recommend you get Navicat for Mysql

It is far superior over phphmyadmin and can run locally on your computer as opposed to needing a server to run it. If you were a Carpenter, Navicat would be the hammer in your tool box.

Since you are inexperienced at this time you probably don’t know about or understand the “Relationship” part of Relational Databases. If you would like to provide your DB schema, we can go over it and you can learn some more really neat things you can do. Again, I dont need your actual data, just the sql to recreate your database.

[code]CREATE TABLE score_results
id int auto_increment primary key,
schoolID int,
team1_score int,
opponentID int,
team2_score int

INSERT INTO score_results
(schoolID, team1_score, opponentID, team2_score)
(1001, 6, 2005, 4),
(2001, 8, 2010, 3),
(2005, 10, 2010, 5);

CREATE TABLE team_name
id int auto_increment primary key,
schoolName varchar(255)

INSERT INTO team_name
(id, schoolName)
(1001, “Washington”),
(2001, “Taft”),
(2005, “Howard”),
(2010, “Lincoln”);[/code]

Naming the joins was not something that I was familiar with. Didn’t know I was allowed to do that! :slight_smile:

I really appreciate everything that you guys do. I work on my own and I don’t have any resources where I can request help.

I actually didn’t have tables setup with the examples that I provided. I just pulled out fields that were relevant to my question, but I will setup tables in the future.

I would be interested in seeing a few examples of the subqueries. I’m pretty good at duplicating and twisting what others do, but I just haven’t seen many examples that are applicable to what I do.

I’ll be using this code that Jim provided in a variety of ways for my high school tennis web site. (

I will also check out the Navicat software that you suggested. Thank you again for all of your help.

Sponsor our Newsletter | Privacy Policy | Terms of Service