Web Development

Ask something
Discussion Locked

3 table SQL Join Query

0 helpful
7 replies

by dryflies

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Three tables, GameSched, UmpSched, GameLoc.
PK stands for Primary Key, FK is Foreign Key.
GameSched has Day,Date,Time,Home,Away,Location,ID(PK)
GameLoc has Name,Url,ID(PK)
UmpSched has First,Second,Third,Plate,GameNo(FK),ID(PK)

The following query retrieves an Empty Set. However, I can query GameSched with GameLoc or GameSched with UmpSched and retrieve the appropriate records. I know I need a JOIN but am not sure about the syntax.
Any Suggestions?
$query_AllUmpGames = "SELECT GameSched.`Day`, GameSched.`Date`, GameSched.`Time`, GameSched.Division, GameSched.Home, GameSched.Visitor, GameSched.Location, GameSched.ID, GameLoc.`url`, UmpSched.Plate, UmpSched.First, UmpSched.Second, UmpSched.Third, UmpSched.GameNo FROM GameSched, diamonds, UmpSched WHERE ( UmpSched.GameNo = GameSched.ID ) && ( `GameSched`.`Location` LIKE `GameLoc`.`name` )";

7 replies

by master3bs

FROM (GameSched INNER JOIN GameLoc ON GameSched.Location = GameLoc.ID), (UmpSched INNER JOIN GameSched on UmpSched.GameNo = GameSched.ID)

by tony hopkinson

Or
FROM GameSched, GameLoc, UmpSched
WHERE ( UmpSched.GameNo = GameSched.ID ) and ( `GameSched`.`Location` = `GameLoc`.`name` )

Where did diamonds come from ?

by tony hopkinson

There's something wrong with your data or possibly datatypes then.
Given you actually defined the FK, I'd suspect Gamesched.location = GameLoc.name
Put some test data in that just has to work
GameNo of 1 and Location = 'A'. The only other outside possibilty I can see for the string data would be a different collation which can cause problems on occasion. Thinking about it Char and varchar would do it so would char(10) and Char(20) or some such, chars are packed out to their length.

by dryflies

diamonds is a misprint on my part. is synonymous with GameLoc. on the first iteration, I used diamonds because fields caused an error in the make table query.

BTW, both queries return an empty set.

This discussion is closed to new replies.