Page 1 of 1

SQL Select Statement from Multiple Tables

Posted: Fri Jan 06, 2006 6:58 am
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

Posted: Fri Jan 06, 2006 7:52 am
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

Posted: Fri Jan 06, 2006 8:01 am
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

Posted: Fri Jan 06, 2006 12:39 pm
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());
         }

Posted: Sat Jan 07, 2006 11:41 am
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

Posted: Sat Jan 07, 2006 11:53 am
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 :)

Posted: Sat Jan 07, 2006 12:45 pm
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;

Posted: Sat Jan 07, 2006 1:27 pm
by Jenk

Code: Select all

SELECT `GameInformation`.`GameNumber` ...

Posted: Sat Jan 07, 2006 1:33 pm
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

Posted: Sat Jan 07, 2006 3:35 pm
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...

Posted: Sat Jan 07, 2006 4:27 pm
by dwessell
Hey Tim..

I've been reading it all day ;)

Thanks for the help..