SQL question

Anon

This will probably be easy for some but hard for others like myself who dont see this very much.

Here is the question. A person logs in and has a certain id associated with his profile, not his user id but his team id, I then need to match that teamid with one of two fields in the games table either the visitor id or home id, I then must take the date field, I must take the visitor and home id and match those up to teams names in another database, and then I need to take the field id and match that up to a Field name in the fields table. The most difficult part for me is getting to do this thing in the smallest amount of space and then actually constructing the SQL to query the games table, adnd taking one id and trying to find which one it matches up with in that table.

Help ... please

dan

Anon

Oh and its MySQL ... sorry

Anon

I'm not sure what you're trying to do. Perhaps you could list your tables and fields?

You say that "the most difficult part is . . . [doing] this thing in the smallest amount of space". Have you been able to do it yet with any "amount of space"?

Anon

I think the best is an example of some output I'm trying to get, then list the tables and then my sql statment so far.
What happens. A manager of a team logs in, he needs to record the score of the game. So in his user table he has the team that he is associated with. He logs in he is presened with a dropdown prefilled with all games that have his team playing in it, and he must select what game he is recording the score for. The expected output is this Mets .vs Braves - 4/01/02 @ Downs Field, this data is grabbed from 3 tables. games, teams, and _fields. Here are those three tables.

games
gameid gamenum gamedate homescore visitorscore homeid visitorid fieldid

1 1 2002-04-01 10:00:00 NULL NULL 3 2 1
2 2 2002-04-01 10:00:00 NULL NULL 4 1 2
3 3 2002-04-01 13:00:00 NULL NULL 5 6 1
4 4 2002-04-01 13:00:00 NULL NULL 7 8 2
5 5 2002-04-02 19:00:00 NULL NULL 6 3 1

teams
id teamname division

1 Astros C
2 Braves A
3 Mets A
4 Orioles C
5 Expos M
6 Cardinals M
7 Cardinals C
8 White Sox C

_fields
id fieldname

1 Downs
2 LBJ

here is the SQL that I have worked on. I could be wayyyyyy off, but any help would be appreciated. "SELECT * FROM games LEFT JOIN teams ON (games.homeid=teams.id) AND ON (games.visitorid=teams.id) LEFT JOIN fields ON (games.fieldid=fields.id) WHERE games.homeid = '$TeamID' OR games.visitorid = '$TeamID'";
$TeamID is a session variable that is pulled from his user profile.

Anon

I don't think your gonna be able to do it in one select statement.

I had to select all the games where TEAM X was the home or away, then once I had that get the team names and field name inside the loop.

I've done a similar project for an AYSO soccer Region. It was easier for me to break out some of the selects in to separate statements

-Paul

[deleted]

"I don't think your gonna be able to do it in one select statement."

Why not, he already has all the game data in the first table, and just want's to replace the ID's with the real data.

Just one snag; the teams table must be joined twice to be able to get two different rows from it in one JOINed output.

Try something like this:

SELECT teams1.teamname as hometeam,
teams2.teamname as visitorteam,
fields.fieldname as fieldname,
fames.gamedate
FROM games, teams as hometeams, teams as visitorteams, fields
WHERE (games.homeid=hometeams.id)
AND (games.visitorid=visitorteams.id)
AND (games.fieldid=_fields.id)
WHERE games.homeid = $TeamID
OR games.visitorid = $TeamID

Anon

ah, learn something new everyday 🙂

Except I couldn't get it to work with that second WHERE clause. I had to replace it with AND

-Paul

[deleted]

Woops, I really should not post when I'm low on caffeine :-)

that WHERE should indeed have been an AND...

...I spent to much time playing RTCW.. I just looove that sniper-rifle :-)

Anon

I figured it was a typo but after my last bold statement I wasn't taking any chances 🙂

[deleted]

:-)

Anon

cold medicine has the same effect, light headed, forgetful. Ah anyway, I looked at your SQL and ran it and I got a could execute query. Heres what I have.

"SELECT teams1.teamname as hometeam, teams2.teamname as visitorteam, fields.fieldname as fieldname, games.gamedate FROM games, teams as hometeams, teams as visitorteams, fields WHERE (games.homeid=hometeams.id) AND (games.visitorid=visitorteams.id) AND (games.fieldid=_fields.id) AND (games.homeid = $TeamID OR games.visitorid = $TeamID)";

any thoughts ... the fields table name is _fields. so I made those adjustments, and replaceing the second WHERE clause with AND.

Anon

question is how are you coming up with this?
teams1.teamname and teams2.teamname. oh just in case heres the whole sql event.

$sql = "SELECT teams1.teamname as hometeam, teams2.teamname as visitorteam, fields.fieldname as fieldname, games.gamedate FROM games, teams as hometeams, teams as visitorteams, fields WHERE (games.homeid=hometeams.id) AND (games.visitorid=visitorteams.id) AND (games.fieldid=_fields.id) AND games.homeid = $TeamID OR games.visitorid = $TeamID";
$sql_result = mysql_query($sql) or die("Couldn't execute query.");
while ($row = mysql_fetch_array($sql_result)) {

Anon

replace "SELECT teams1.teamname as hometeam, teams2.teamname as visitorteam..."

with "SELECT hometeams.teamname as hometeam, visitorteams.teamname as visitorteam..."

Because "teams1" and "teams2" are defined anywhere. I noticed this when I was trying it but forgot to mention it in my previous post.

-Paul
(on my thrid cup of coffee 🙂 )

Anon

Because "teams1" and "teams2" are

that really should be "aren't"

Anon

okay are you dipping into my cold medicine.

heheheh 🙂

Anon

Okay its perfect, one thing I had to fix was cause I wanted only the games where a the users team was playing I needed to add () around the last AND clause. so the final query looks a little like this.

	$sql = "SELECT hometeams.teamname as hometeam, visitorteams.teamname as visitorteam, _fields.fieldname as fieldname, games.gamedate FROM games, teams as hometeams, teams as visitorteams, _fields WHERE (games.homeid=hometeams.id) AND (games.visitorid=visitorteams.id) AND (games.fieldid=_fields.id) AND (games.homeid = $TeamID OR games.visitorid = $TeamID)";

thanks

dan