SQL Select Statement from Multiple Tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dwessell
Forum Commoner
Posts: 62
Joined: Fri Dec 23, 2005 2:30 pm

SQL Select Statement from Multiple Tables

Post by dwessell »

Hey all.. I'm having issues finding the syntax for using Multiple Table information as a WHERE statement.. And would be much obliged if someone could point me in the right direction..

I have table, GameInformation and TeamInformation.. I'm looking for something like I have below, but I'm having issues with the syntax.

Code: Select all

$query = "SELECT `Game Number` FROM `TeamInformation` WHERE `PhoneNumber(InTeamInformation)` = $variable AND `Game Number(InGameInformation` = (The Game Number we just pulled out) AND `GameStatus(InGameInformatino)` = 1";
Whew..

Thanks
David
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

If you want to do this, their should be a relation between your tables. (Eg: Probably your gameinformation knows to which team it is related, gameinfo has a column team_id)

Code: Select all

SELECT something
FROM teaminformation LEFT INNER JOIN gameinformation USING (team_id)
WHERE teaminformation.name ='timvw is our hero' AND gameinformation.status = 'VICTORY'
You can read more about joins at: http://www.w3schools.com/sql/sql_join.asp
dwessell
Forum Commoner
Posts: 62
Joined: Fri Dec 23, 2005 2:30 pm

Post by dwessell »

That's perfect, thanks for pointing me there.

Thanks
David

timvw wrote:If you want to do this, their should be a relation between your tables. (Eg: Probably your gameinformation knows to which team it is related, gameinfo has a column team_id)

Code: Select all

SELECT something
FROM teaminformation LEFT INNER JOIN gameinformation USING (team_id)
WHERE teaminformation.name ='timvw is our hero' AND gameinformation.status = 'VICTORY'
You can read more about joins at: http://www.w3schools.com/sql/sql_join.asp
dwessell
Forum Commoner
Posts: 62
Joined: Fri Dec 23, 2005 2:30 pm

Post by dwessell »

Hey all.. I was thinking incorrectly in my first statement.. It would appear that this needs to be two SQL statements, since I don't know the GameNumber, until I run the statement..

In the first statement, I find the Game Number (There could be more then one game, tied to a phone number)..

In the second statement, I'm trying to check the Gamenumbers, to make sure that the status = 1 (Only one phone number per active game at a time)..

But my for loop to iterate through any Game Number's would overwrite itself each time.. Not really gonna work :)

Any help would be appreciated.

As always, I apprecitate the help on the forums, it makes learning a new language much easier..

Thanks
David

Code: Select all

$query = "SELECT `Game Number` FROM `TeamInformation` WHERE `PhoneNumber` = $subj";
	$resultOne   = mysql_query($query) or die(mysql_error());
	$row = mysql_fetch_array($resultOne,MYSQL_NUM) ;
	$size = count($row);
	
	for($i=0;$i<$size;$i++){
		$queryTwo = "SELECT `Game Number` FROM `Game Information` WHERE `Game Number` = $row[0] AND `Status` = 1";
		$resultTwo = mysql_query($queryTwo) or die(mysql_error());
         }
dwessell
Forum Commoner
Posts: 62
Joined: Fri Dec 23, 2005 2:30 pm

Post by dwessell »

In an effort to clarify, as much for myself as anyone reading this thread.

What I have is this:

In Table 1
GameNumber (Unique)
Status (Either 1 or 0)

In Table 2
PhoneNumber
GameNumber (References Table 1)

I'm trying to query Table 2, pull out the GameNumber where PhoneNumber matches my variable..

Then reference that GameNumber, to Table 1.. And weed out any GameNumbers where Status is not 1.

The tricky thing (at least for me) is I'm trying to do this one one statement for various other reasons that I won't go into here..

I'm been trying to use an InnerJoin, but haven't had any luck.. I'm parsing an email where the PhoneNumber variable is all that I have.. So I tie that variable to a GameNumber.. But I could have a phone Number that is tied to multiple games... So I need to query with the gamenumbers returned, to see which has a status of 0....


Thanks
David
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I'm trying to query Table 2, pull out the GameNumber where PhoneNumber matches my variable..

Code: Select all

SELECT GameNumber
FROM Table2
WHERE PhoneNumber = '$variable';
Then reference that GameNumber, to Table 1.. And weed out any GameNumbers where Status is not 1.

Code: Select all

SELECT GameNumber
FROM Table2 INNER JOIN Table1 USING (GameNumber)
WHERE PhoneNumber = '$variable' AND Status <> 1;
I'm been trying to use an InnerJoin, but haven't had any luck.. I'm parsing an email where the PhoneNumber variable is all that I have.. So I tie that variable to a GameNumber.. But I could have a phone Number that is tied to multiple games... So I need to query with the gamenumbers returned, to see which has a status of 0..
Better tell us what you want to do exactly instead of a halfbaked algorithm :)
dwessell
Forum Commoner
Posts: 62
Joined: Fri Dec 23, 2005 2:30 pm

Post by dwessell »

Hey Tim.. Sorry for the poor explanation :)

Thanks for your help..

I'm being told that (GameNumber) is ambigious.. Which makes sense, as there is a GameNumber field in both tables.. I've tried to specify which table I meant (i.e. (GameInformation.GameNumber)) but I'm getting syntax errors..

Code: Select all

SELECT `GameNumber` FROM `TeamInformation` INNER JOIN `GameInformation` USING (TeamInformation.GameNumber) WHERE `PhoneNumber` = $subj AND Status <> 1;
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Code: Select all

SELECT `GameInformation`.`GameNumber` ...
dwessell
Forum Commoner
Posts: 62
Joined: Fri Dec 23, 2005 2:30 pm

Post by dwessell »

Jenk wrote:

Code: Select all

SELECT `GameInformation`.`GameNumber` ...

Hey Jenk..

The error that I'm getting is based around the

Code: Select all

USING (TeamInformation.GameNumbe )
part.. I'm looking now through the MySQL docs attemting to find an anwer.. Any suggestions are helpful, though..

Thanks
David
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Actually, Jenk was right:

Code: Select all

SELECT Table2.GameNumber
FROM Table2 INNER JOIN Table1 USING (GameNumber)
WHERE PhoneNumber = '$variable' AND Status <> 1;
But i can only advise you to read the complete section on learning SQL at w3schools.com.. Or get search for a decent tutorial or book elsewhere... Because every programming job you'll later have to complete will probably require you to know SQL, so it's worth to do the initial investment...
dwessell
Forum Commoner
Posts: 62
Joined: Fri Dec 23, 2005 2:30 pm

Post by dwessell »

Hey Tim..

I've been reading it all day ;)

Thanks for the help..
Post Reply