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.