Selecting from two tables without JOIN

[jc94062]

I know about LEFT and RIGHT JOINS but I'm trying to generate a list that I can't use this for, in the list should be all the names that have either
level='Level 13' OR level='14' OR level='15' from table1
OR
level='11' OR level='12' from table2

so I can't use joins as if I do the name has to be present in both table1 and table2 to give a result,
ie - SELECT * FROM table2 LEFT JOIN table1 ON table2.name=table1.name

is there a way to do this? I've tried things such as SELECT from table1,table2 but of course as the field 'level' is present in both table this also doesn't give a result. Trying
SELECT from table1,table2 WHERE table1.level='Level 13' OR table2.level='Level 11'

also doesn't work :-(

Thanks for any help

[superslacker]

Looks like what you're looking for here is a union...

(select name from table1 where level= . . .)
UNION
(select name from table2 where level= . . .)

Give that a try.

[jc94062]

Heres the code

("SELECT name FROM table1 WHERE level='Level 15' OR level='Level 14' OR level='Level 13'",$db);
UNION
("SELECT name FROM table2 WHERE level='Level 12' OR level='Level 11'",$db);

and I get

Fatal error: Call to undefined function: union() in /home/1030/ego/www.eliteguard.co.uk/public_html/rsupdate.php on line 24

where line 24 is the second SELECT statement :-(

[superslacker]

The whole thing is one SQL statement. So, for example:

$sqlStmt = "(select name from table1 where level=13) UNION (select name from table2 where level=12)";
// run query

UNION is a SQL construct and not PHP. My apologies for not making that clear. Hope it works this time.

[jc94062]

Heres the <i>actual</i> statement I have in my script

<? $username2 = "(SELECT zonename FROM roster WHERE level=Level 14) UNION (SELECT zonename FROM rsroster WHERE level=Level 12)",$db; ?>

Gives me a parse error now, I don't know why is won't work, I've tried adding brackets in, moving bits around, putting quotations around the Level 14 and Level 12 bits but it didn't help :-(

[bmpco]

<? $username2 = "(SELECT zonename FROM roster WHERE level=Level 14) UNION (SELECT zonename FROM rsroster WHERE level=Level 12)",$db; ?>

the parse erro occurs because you have the

,$db

after the "

you should use your string as follows:

$username2 = "(SELECT zonename FROM roster WHERE level=Level 14) UNION (SELECT zonename FROM rsroster WHERE level=Level 12)";

and run it as

mysql_query ( $username2 );

and that should solve your problem..

if you need to specify the database link, use something like

mysql_query ( $username2, $db );

..

[jc94062]

Adding error checking now...

$username2 = "(SELECT zonename FROM roster WHERE level=Level 14) UNION (SELECT zonename FROM rsroster WHERE level=Level 12)";
mysql_query ($username2, $db);
if ($username2) {echo "Yey!";}
else {
echo("<P>Error: " .
mysql_error() . "</P>");
exit;}

displays Yey! and then a pull down menu with nothing in it, the only other way I've been able to get close was this...

$username2 = mysql_query("SELECT * FROM rsroster,roster WHERE roster.level='Level 14' OR rsroster.level='Level 12'",$db);

displays the entry from the roster table

$username2 = mysql_query("SELECT * FROM roster,rsroster WHERE roster.level='Level 14' OR rsroster.level='Level 12'",$db);

displays the entry from the rsroster table

so for some reason that way only selects from the second table I've specified. This must be possible?

[jc94062]

It finally works, the person to be selected HAS to be present in roster table whatever...

$username2 = mysql_query("SELECT roster.zonename FROM roster,rsroster WHERE roster.level='Level 15' OR roster.level='Level 14' OR roster.level='Level 13' OR rsroster.level='Level 12' OR rsroster.level='Level 11'",$db);

:-)