Page 2 of 2
Re: Is this query written the best way possible?
Posted: Wed Mar 09, 2011 4:35 am
by JustPlainJef
Vlad, much appreciation for your assistance on this problem!
Re: Is this query written the best way possible?
Posted: Wed Mar 09, 2011 6:11 am
by JustPlainJef
Code: Select all
SELECT
schedules2.GameDate,
schedules2.GameTime,
schedules2.GameField,
divisions.DivisionName,
AwayTeam.Color AS AwayTeamColor,
AwayTeam.Manager AS AwayTeamManager,
HomeTeam.Color AS HomeTeamColor,
HomeTeam.Manager AS HomeTeamManager
FROM
schedules2
INNER JOIN
teams AS AwayTeam
ON schedules2.AwayTeamID = AwayTeam.TeamID
INNER JOIN
teams AS HomeTeam
ON schedules2.HomeTeamID = HomeTeam.TeamID
INNER JOIN
divisions
ON HomeTeam.DivisionID = divisions.DivisionID
WHERE
schedule2.GameDate = CURDATE()
ORDER BY
GameID
One little problem on the last join, it was DivisionID on both sides, I (we) had DivisionName on the right.
And a question.....
When I did "WHERE divisions.DivisionName = Senior" it doesn't work, but if I do 'WHERE divisions.DivisionID = 4" it works. Why can't I run the query on the division name?In divisions, ID.4 = Name.Senior (to steal some of the writing convention). Is it because the join is on DivisionID?
Re: Is this query written the best way possible?
Posted: Wed Mar 09, 2011 6:20 am
by VladSun
You need quotes when working with strings:
Code: Select all
WHERE divisions.DivisionName = 'Senior'
Also, it's a good idea to "escape" column/table names in order to avoid SQL keywords conflicts.
Code: Select all
WHERE `divisions`.`DivisionName` = 'Senior'
Re: Is this query written the best way possible?
Posted: Wed Mar 09, 2011 6:32 am
by JustPlainJef
That was my second guess, but when I saw that 4 and CURDATE() worked without them, I thought maybe it wasn't the quotes.
I'll try that now.
Once again, you ROCK!
Re: Is this query written the best way possible?
Posted: Wed Mar 09, 2011 6:42 am
by JustPlainJef
I know this works...
WHERE AwayTeam.Manager = 'Foo'
OR HomeTeam.Manager = 'Foo'
Is there a way to get that down to one line?
I tried teams.Manager, and that didn't work. Can we do AwayTeam.Manager OR HomeTeam.Manager = 'Foo'?
Re: Is this query written the best way possible?
Posted: Wed Mar 09, 2011 7:00 am
by VladSun
Code: Select all
WHERE
AwayTeam.Manager = 'Foo'
OR
HomeTeam.Manager = 'Foo'
That's good enough.