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

Is this query written the best way possible?

Post by JustPlainJef »

Hello all.

I got my query to work , but as always, I'm looking for any and all advice on how to make it work better. And I'm not exactly clear on why I had to reference the tables in two different ways to get it to work....

I do know I can select fields instead of *, but other than that, do you see anything?

Table.schedules2 (it's called 2 because I wanted to keep the original in tact for testing)
GameID | GameDate | GameTime | GameField | AwayTeamID | AwayTeamScore | HomeTeamID | HomeTeamScore | Rainout

Table.teams
TeamID | DivisionID | Color | Manager | Win | Loss | Tie | WinPercentage

Table.divisions
DivisionID | DivisionName | AgeLower | AgeUpper

Query:

Code: Select all

<?php
SELECT * FROM schedules2 s, teams t1, teams t2, division  d1, division d2
WHERE s.AwayTeamID = t1.TeamID
AND s.HomeTeamID = t2.TeamID
AND t1.DivisionID = d1.DivisionID
AND t2.DivisionID = d2.DivisionID
ORDER BY GameID
?>
I have realized that I only need the division once, so that could eliminate d2, but I'd still rather learn more than I need to do what needs to be done.
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: Is this query written the best way possible?

Post by Darhazer »

use explicit joins instead of implicit, and run explain on your query to check if you are using the correct indexes
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 »

What do you mean explicit / implicit?

*runs off to Google to also look it up*
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 »

IMHO, Darhazer meant to use INNER JOIN together WITH an ON clause instead of commas together with a WHERE clause.
There are 10 types of people in this world, those who understand binary and those who don't
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 »

JustPlainJef wrote:I have realized that I only need the division once, so that could eliminate d2, but I'd still rather learn more than I need to do what needs to be done.
Are you sure about that? What will happen if the two teams are in different divisions?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: Is this query written the best way possible?

Post by Darhazer »

VladSun wrote:IMHO, Darhazer meant to use INNER JOIN together WITH an ON clause instead of commas together with a WHERE clause.
QFT
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 »

VladSun wrote:
JustPlainJef wrote:I have realized that I only need the division once, so that could eliminate d2, but I'd still rather learn more than I need to do what needs to be done.
Are you sure about that? What will happen if the two teams are in different divisions?
They can't be in different divisions. I'll set up another set of queries to run once to make sure that we don't have any cross-divisional games, and make sure that AwayTeam != HomeTeam.

I'm still reading about the explicit joins, had a few things come up.
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 »

I would advice you not to use aliases for name shortcuts. (like table t1)
This

Code: Select all

teams as homeTeams
teams as awayTeams
is much better IMHO. Don't use aliase when not needed.
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 »

How's this look for today's schedule (just so I could get a "WHERE" in there)?

**Edited based on the above post, and below post**

Table.schedules2
GameID | GameDate | GameTime | GameField | AwayTeamID | AwayTeamScore | HomeTeamID | HomeTeamScore | Rainout

Table.teams
TeamID | DivisionID | Color | Manager | Win | Loss | Tie | WinPercentage

Table.divisions
DivisionID | DivisionName | AgeLower | AgeUpper

Query:

Code: Select all

SELECT GameDate, GameTime, GameField, divisions.DivisionName, AwayTeam.Color, AwayTeam.Manager, HomeTeam.Color, HomeTeam.Manager 
FROM schedules2
LEFT JOIN teams AS AwayTeam
ON schedules2.AwayTeamID = AwayTeam.TeamID
LEFT JOIN teams AS HomeTeam
ON schedules2.HomeTeamID = HomeTeam.TeamID
LEFT JOIN divisions
ON HomeTeam.DivisionID = divisions.DivisionName
WHERE s.GameDate = CURDATE()
ORDER BY GameID
Can I put the division name before the team name? Does it matter in what order the SELECT fields are typed?

I'm still working on the indexing and the EXPLAIN, but I've got some reading...
Last edited by JustPlainJef on Tue Mar 08, 2011 11:55 am, edited 1 time in total.
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 »

Please, use
<syntax=sql>
... SQL here ...
</syntax>

and no <?php ?> trags - it's not PHP :)
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 »

VladSun wrote:Please, use
<syntax=sql>
... SQL here ...
</syntax>

and no <?php ?> trags - it's not PHP :)
Done. See how much I'm learning!!!

:oops:
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've missed the aliases for same-name-columns in the SELECT clause. Obviously, it's also a matter of style but I think indentation is a must:

Code: Select all

SELECT 
    GameDate, 
    GameTime, 
    GameField, 
    divisions.DivisionName, 
    AwayTeam.Color as AwayTeamColor, 
    AwayTeam.Manager as AwayTeamManager, 
    HomeTeam.Color as HomeTeamColor, 
    HomeTeam.Manager as HomeTeamManager
FROM 
    schedules2
LEFT JOIN 
    teams AS AwayTeam
        ON schedules2.AwayTeamID = AwayTeam.TeamID
LEFT JOIN 
    teams AS HomeTeam
        ON schedules2.HomeTeamID = HomeTeam.TeamID
LEFT JOIN 
    divisions
        ON HomeTeam.DivisionID = divisions.DivisionName
WHERE 
    s.GameDate = CURDATE()
ORDER BY 
    GameID
Also ordering by GameID doesn't make sense to me, probably

Code: Select all

ORDER BY
    GameTime
is more appropriate but it's business logic.

PS: Where is the team.name column? :)
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 »

There is no TeamName. They got taken away a few years ago...

Thanks for the info. When I get back to a PC, I'll look over mine compared to yours.

And I sorted by GameID since the last query I was working on was GameID, but yes, it probably will be GameDate, GameTime when I list out the whole schedule.

Thanks a bunch! I'll be back!
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 »

Morning all (or afternoon, or evening.....)

Vlad, after looking at your syntax, and discussing it with a friend, I'm feeling much better about it. Thank you.

Here would be my final write. I don't know if I'd use this particular query, but I will have many similar, and learning is learning....

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.DivisionName
WHERE 
    schedule2.GameDate = CURDATE()
ORDER BY 
    GameID
My friend suggested adding the table name to all of the select elements just to keep them consistent and make it a bit easier to read. Also, we discussed it last night and I don't think I need the LEFT JOIN. I'm still reading up on the differences, but that's what learning / practice is for.

When I dump this into PHP, can I write it like this, with the line breaks and the indents? I do use the line breaks and indents in my coding for readability.
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 »

JustPlainJef wrote:Also, we discussed it last night and I don't think I need the LEFT JOIN.
That's true. I simply forgot to write it in my post.
JustPlainJef wrote:When I dump this into PHP, can I write it like this, with the line breaks and the indents? I do use the line breaks and indents in my coding for readability.
yes :)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply