Multiple tables

[TEENFRONT]

Hey Hey

I have a database with 3 tables. Tables are, users1, users2, and users3. to make things simple lets say they all have 1 column "username".

I wanna do this
$username = "Adam";
$sql ="SELECT username FROM user1, user2, user3 WHERE username = '$username'";


But the username, Adam, is only in 1 of the 3 tables. So the above doesnt work, it just fails to find Adam. Yet if i just do it with 1 table like this..

$username = "Adam";
$sql ="SELECT username FROM user1 WHERE username = '$username'";

it will find Adam if Adam is in users1.

I need to be able to locate Adam from all 3 tables. How do i do this?

[corbin]

You could do 3 different queries... or a join query... but im half asleep so i cant think of how to do that :P

[TEENFRONT]

i was kinda hoping i wouldnt have to do 3 queries..

username is unique in all 3 tables.. so surley it can select username from 3 tables and return 1 result?

[brown2005]

well i thought it was this.....

$username = "Adam";

$sql = "SELECT user1.username AS user1, user2.username AS user2, user3.username AS user3 FROM user1, user2, user3 WHERE user1.username = '$username' OR user2.username = '$username' OR user3.username = '$username';";
$results = mysql_query($sql) or die(mysql_error());

while($array = mysql_fetch_array($results))
{

echo $array['user1'];

echo"<br><br>";

echo $array['user2'];

echo"<br><br>";

echo $array['user3']; 
}

but it only works if Adam is in all 3 tables.... so maybe someone could help

[alpine]

[code]

(SELECT username FROM user1 WHERE username = '$username')
UNION (SELECT username FROM user2 WHERE username = '$username')
UNION (SELECT username FROM user3 WHERE username = '$username')

[/code]