Multi table select query...

[The_Chancer]

I am trying to work out whether it is possible to do the following:

SELECT table1.product,table1.category,table1.price,table2.product,table2.category,table2.price FROM table1,table2 WHERE table1.category=this OR table2.category=this

The query seems to run, with no errors, but there are no answers given, even though I know there should be.

Any ideas ?

full code as below

$query=do_query("SELECT company FROM suppliers");
$i=0;
$qu="SELECT ";
while($company=mysql_fetch_assoc($query))
{
	foreach($company as $table)
	{
		$qu .= $table.".productname, ";
		$qu .= $table.".category, ";
		$qu .= $table.".proddescription, ";
		$qu .= $table.".price, ";
		$qu .= $table.".loresimage, ";
		$qu .= $table.".techspec,";
	}
}
	$qu=substr($qu,0,-1);
	$qu .= " FROM ";
$query=do_query("SELECT company FROM suppliers");
while($company=mysql_fetch_assoc($query))
{
	foreach($company as $table)
	{
		$qu .= $table.",";
	}
}
	$qu=substr($qu,0,-1);
	$qu .= " WHERE ";
$query=do_query("SELECT company FROM suppliers");
while($company=mysql_fetch_assoc($query))
{	
	foreach($company as $table)
	{
		$qu .= $table.".category='Loudspeakers' OR ";
	}
}
$qu=substr($qu,0,-4);
	echo $qu;
	$query=do_query($qu);	
	//$qu=do_query("SELECT * FROM $table");
	while(list($prod,$cat,$prodd,$price,$lores,$tech) = mysql_fetch_row($query)) 

(criticism of the reuse of the same statement is also acceptable... if there is another way round this that makes it shorter and much nicer to look at and read, then let me know !!)

Cheers,

[barand]

Time to call in the union.

Try

$sql = "SELECT table1.product,table1.category,table1.price FROM table1 WHERE table1.category=this " .
" UNION " .
"SELECT table2.product,table2.category,table2.price FROM table2 WHERE table2.category=this " ;

hth

[The_Chancer]

Cheers for the reply...

I found an easier way round it though, but good to know for the future...

[The_Chancer]

I wonder - does the UNION statement also have the LIMIT function available at the end of it ?

As in

$query="(SELECT a FROM table_name WHERE a=10 AND B=1)
UNION
(SELECT a FROM table_name WHERE a=11 AND B=2)
LIMIT x,y";

Hmmm