I have one mysql table called 'games' with the fields: id, team1, team2, and date. I have a second mysql table called 'teams' with the fields: id, name, grade.
My goal is to select all 'games' with date = March 1st, but then ALSO where grade = 10th.
So I imagine I need some kind of subquery but I don't know sql well enough. Any help appreciated.
All I know to do so far is:
("SELECT * FROM games WHERE date = '2012-03-01'")
Thanks so much. Also I do want a single sql statement...
I'm assuming games.team1 is related to teams.id, as is games.team2.
You just have to bring your teams table into the query with a JOIN.
Since you have two columns in games that are related to teams by the teams.id column, you have to use two joins:
SELECT games.*
FROM games
JOIN teams t1 ON games.team1 = t1.id
JOIN teams t2 ON games.team2 = t2.id
WHERE date = '2012-03-01'
AND t1.grade = 10
AND t2.grade = 10
I've specified that both teams be in grade 10, but you could change that to an OR if you wanted at least one of the teams being in grade 10. (say there was a game with a grade 12 team vs a grade 10 team).
You'll want to use a JOIN to solve this. This solution makes the assumption that "team1" and "team2" represent "id" from the teams table.
SELECT g.*
FROM games g
INNER JOIN teams t1 ON t1.id = g.team1
INNER JOIN teams t2 ON t2.id = g.team2
WHERE g.date = '2012-03-01'
AND t1.grade = "10th"
AND t2.grade = "10th";
If teams can only play other teams from the same grade, then you could get away with only checking one team's grade (instead of both).
SELECT g.* FROM games g
JOIN teams t ON t.grade = 10 AND (t.id = g.team1 OR t.id = g.team2)
WHERE g.date = '2012-03-01'
if games.team1 and games.team2 are id values from teams then
SELECT *
FROM games
INNER JOIN teams ON games.team1 = teams.id WHERE date = '2012-03-01' AND teams.grade=10
UNION
SELECT *
FROM games
INNER JOIN teams ON games.team1 = teams.id WHERE date = '2012-03-01' AND teams.grade=10
will give you any games where one team is grade 10.
If you want to have only paired teams of grade 10 then
SELECT *
FROM games
INNER JOIN teams T1 ON games.team1 = T1.id
INNER JOIN teams T2 ON games.team2 = T2.id
WHERE T1.grade=10 AND T2.grade=10 AND games.date = '2012-03-01'
If team1 and team2 are names then you need to use games.team1 = T1.name respectively for team1 and team2.
gamesrelated toteams?games.team1andgames.team2are related toteams.id? Is it possible for teams from different grades to be in the same game?graderefering toteam1orteam2or both?games.team1andgames.team2are related toteams.id... Each team has a single grade