Page 1 of 1

Joining tables

Posted: Sun Nov 30, 2003 9:04 pm
by smoky989
OK heres what I'm trying to do.

TABLE1
I have a table that has a list of names in it, it has several fileds but I only need to deal with names and status.

TABLE2
I have another table with the same names along with 3 fields with other information.

I want to list the names and 3 other fields of TABLE2 where the status is equal to 1 or 3. This is how my code looks.

Code: Select all

<?php

$db = mysql_connect("localhost", "username", "password");

mysql_select_db ("blah");

$result = mysql_query("select TABLE1.name,  TABLE2.show, TABLE2.active, TABLE2.notes from TABLE1, TABLE2  where TABLE1.name = TABLE2.name and TABLE1.status = 1 || TABLE1.status = 3 order by name", $db);

echo "<table border =2><tr><td colspan=4><b><div align=center>Testing</div></b></td></tr>";

while($myrow = mysql_fetch_array($result)){
	$name = $myrow["name"]; 
	$show = $myrow["show"]; 
	$active = $myrow["active"]; 
	$notes = $myrow["notes"]; 
	
	echo "<tr><td width=175>";
	echo "$name";
	echo "</td><td>";
	echo "$show";
	echo "</td><td>";
	echo "$active";
	echo "</td><td>";
	echo "$notes";
	echo "</td><tr>";
	
	};
	
	echo "</table>"
	?>
This isn't working for me. Any suggestions?

Posted: Sun Nov 30, 2003 9:31 pm
by infolock
try this :

Code: Select all

$result = mysql_query("select TABLE1.*, TABLE2.* from TABLE2, TABLE1  where TABLE1.name = TABLE2.name and TABLE1.status = 1 || TABLE1.status = 3 order by name", $db); 

echo "<table border =2><tr><td colspan=4><b><div align=center>Testing</div></b></td></tr>"; 

while($myrow = mysql_fetch_array($result)){ 
   $name = $myrow["table2.name"]; 
   $show = $myrow["table2.show"]; 
   $active = $myrow["table2.active"]; 
   $notes = $myrow["table2.notes"]; 
    
   echo "<tr><td width=175>"; 
   echo "$name"; 
   echo "</td><td>"; 
   echo "$show"; 
   echo "</td><td>"; 
   echo "$active"; 
   echo "</td><td>"; 
   echo "$notes"; 
   echo "</td><tr>"; 
    
   }; 
    
   echo "</table>" 
   ?>

Posted: Sun Nov 30, 2003 9:44 pm
by microthick
Are you getting errors with your query?

I think your query should be:

Code: Select all

$result = mysql_query("select TABLE1.name,  TABLE2.show, TABLE2.active, TABLE2.notes from TABLE1, TABLE2 where TABLE1.name = TABLE2.name and (TABLE1.status = 1 or TABLE1.status = 3) order by TABLE1.name", $db);

Posted: Sun Nov 30, 2003 9:52 pm
by infolock
micro, that says to do the same thing i posted, except you are ordering by the names found in table 1 ;)

Posted: Sun Nov 30, 2003 10:04 pm
by microthick
infolock wrote:micro, that says to do the same thing i posted, except you are ordering by the names found in table 1 ;)
No.

1) By bracketing the OR clause, the query will do what he wants. Before, the AND would have taken precidence, and that's not what he wanted.

2) I don't believe you can use "||" to mean OR in WHERE clauses.

3) Not stating TABLE1 in the order by clause may have caused MySQL to spit up an ambiguity error. I have more experience with MS SQL than MySQL and I know that in MS SQL, it would have errored. Not sure about MySQL.

Posted: Sun Nov 30, 2003 10:14 pm
by infolock
No.

1) By bracketing the OR clause, the query will do what he wants. Before, the AND would have taken precidence, and that's not what he wanted.

2) I don't believe you can use "||" to mean OR in WHERE clauses.

3) Not stating TABLE1 in the order by clause may have caused MySQL to spit up an ambiguity error. I have more experience with MS SQL than MySQL and I know that in MS SQL, it would have errored. Not sure about MySQL
|| is the same as or :) i tried this out on my own table in mysql, and it worked the same both ways.

you are right by if you select both tables, it will give an ambiguity error if you don't specify the table1.name, but you could remove table1.* and it would work fine with just name ;)


edit ::
MySQL Language Reference

OR
||
Logical OR. Evaluates to 1 if any operand is non-zero, to NULL if any operand is NULL, otherwise 0 is returned.

http://www.mysql.com/documentation/mysq ... #Reference

hmmm

Posted: Sun Nov 30, 2003 10:45 pm
by smoky989
Well the parenthesis were a problem but I'm still getting an error.

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

on line 11 which is the line that the where clause is on. I'm gonna check the syntax again to make sure I have everything like you had it. Thanks

Posted: Sun Nov 30, 2003 11:39 pm
by infolock
try this and see if you can get it to give you an error :

Code: Select all

$result = mysql_query("select TABLE2.* from TABLE2, TABLE1  where TABLE1.name = TABLE2.name and TABLE1.status = 1 || TABLE1.status = 3 order by name", $db) or die(MySQL_Error());

Posted: Mon Dec 01, 2003 12:17 am
by smoky989
ok I did that and the error it gave was

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'where TABLE1.name = TABLE2.name and TABLE1.status =

Posted: Mon Dec 01, 2003 12:28 am
by infolock
did you try switching the queries around ?

like trying this :

Code: Select all

$result = mysql_query("select TABLE1.*, TABLE2.* from TABLE2, TABLE1  where TABLE1.name = TABLE2.name and TABLE1.status = 1 || TABLE1.status = 3 order by TABLE2.name", $db) or die(MySQL_Error());
and

Code: Select all

$result = mysql_query("select TABLE1.name, TABLE2.name, TABLE2.show, TABLE2.active, TABLE2.notes from TABLE2, TABLE1  where TABLE1.name = TABLE2.name and TABLE1.status = 1 || TABLE1.status = 3 order by TABLE2.name", $db) or die(MySQL_Error());
etc..

Posted: Mon Dec 01, 2003 2:17 am
by JAM
Just adding alternative syntax;

Code: Select all

select 
	TABLE1.name, 
	TABLE2.name, 
	TABLE2.show, 
	TABLE2.active, 
	TABLE2.notes 
from 
	TABLE1
	inner join TABLE2 on TABLE1.name = TABLE2.name
where 
	TABLE1.status = '1' or TABLE1.status = '3' 
order by 
	TABLE2.name