Page 1 of 2

Is this query written the best way possible?

Posted: Tue Mar 08, 2011 8:28 am
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.

Re: Is this query written the best way possible?

Posted: Tue Mar 08, 2011 8:39 am
by Darhazer
use explicit joins instead of implicit, and run explain on your query to check if you are using the correct indexes

Re: Is this query written the best way possible?

Posted: Tue Mar 08, 2011 9:11 am
by JustPlainJef
What do you mean explicit / implicit?

*runs off to Google to also look it up*

Re: Is this query written the best way possible?

Posted: Tue Mar 08, 2011 9:35 am
by VladSun
IMHO, Darhazer meant to use INNER JOIN together WITH an ON clause instead of commas together with a WHERE clause.

Re: Is this query written the best way possible?

Posted: Tue Mar 08, 2011 9:38 am
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?

Re: Is this query written the best way possible?

Posted: Tue Mar 08, 2011 9:48 am
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

Re: Is this query written the best way possible?

Posted: Tue Mar 08, 2011 9:54 am
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.

Re: Is this query written the best way possible?

Posted: Tue Mar 08, 2011 11:31 am
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.

Re: Is this query written the best way possible?

Posted: Tue Mar 08, 2011 11:42 am
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...

Re: Is this query written the best way possible?

Posted: Tue Mar 08, 2011 11:51 am
by VladSun
Please, use
<syntax=sql>
... SQL here ...
</syntax>

and no <?php ?> trags - it's not PHP :)

Re: Is this query written the best way possible?

Posted: Tue Mar 08, 2011 11:56 am
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:

Re: Is this query written the best way possible?

Posted: Tue Mar 08, 2011 12:00 pm
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? :)

Re: Is this query written the best way possible?

Posted: Tue Mar 08, 2011 12:11 pm
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!

Re: Is this query written the best way possible?

Posted: Wed Mar 09, 2011 3:47 am
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.

Re: Is this query written the best way possible?

Posted: Wed Mar 09, 2011 4:29 am
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 :)