Is this query written the best way possible?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

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?

Post by JustPlainJef »

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?

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Is this query written the best way possible?

Post 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'
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?

Post 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!
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?

Post 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'?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Is this query written the best way possible?

Post by VladSun »

Code: Select all

WHERE 
    AwayTeam.Manager = 'Foo'
    OR 
    HomeTeam.Manager = 'Foo'
That's good enough.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply