Is this query written the best way possible?
Moderator: General Moderators
-
JustPlainJef
- Forum Commoner
- Posts: 42
- Joined: Tue Jan 04, 2011 5:04 am
- Location: McHenry, IL
Re: Is this query written the best way possible?
Vlad, much appreciation for your assistance on this problem!
-
JustPlainJef
- Forum Commoner
- Posts: 42
- Joined: Tue Jan 04, 2011 5:04 am
- Location: McHenry, IL
Re: Is this query written the best way possible?
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
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?
You need quotes when working with strings:
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'Code: Select all
WHERE `divisions`.`DivisionName` = 'Senior'
Last edited by VladSun on Wed Mar 09, 2011 6:35 am, edited 2 times in total.
There are 10 types of people in this world, those who understand binary and those who don't
-
JustPlainJef
- Forum Commoner
- Posts: 42
- Joined: Tue Jan 04, 2011 5:04 am
- Location: McHenry, IL
Re: Is this query written the best way possible?
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!
I'll try that now.
Once again, you ROCK!
-
JustPlainJef
- Forum Commoner
- Posts: 42
- Joined: Tue Jan 04, 2011 5:04 am
- Location: McHenry, IL
Re: Is this query written the best way possible?
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'?
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?
Code: Select all
WHERE
AwayTeam.Manager = 'Foo'
OR
HomeTeam.Manager = 'Foo'There are 10 types of people in this world, those who understand binary and those who don't