Query 2 tables

[CanMan2004]

Hi all

I have a database called "users" and another called "admins".

At the moment, I have a query to look in the table "users", the query I use is

[code]SELECT * FROM users WHERE area =1[/code]

What I want to do is to look in both the "users" table and also look in "admins" but within the same query. Is this possible or should I just do 2 queries?

Thanks in advance

Dave

[craygo]

Are the 2 tables related in any way??

If they are not relative just use 2 queries because there is nothing to relate the 2. Why don't you just flag a user as an admin in the same table. Make a field called group and have it say user, power, admin, ect...Much easier.

Ray

[CanMan2004]

there not linked, but both contain a username and password that I want to show on screen, so by doing 2 tables in one query, it should return all the users that I hold

[swatisonee]

Umm this probaby wont work. just saw your 2nd post.

Try using UNION if theres a common field.  I think this post is what you are lookgin for ?

http://www.phpfreaks.com/forums/index.php/topic,110449.0.html

[CanMan2004]

The tables both already have data, so I cant combine them, the structure looks like

users table
ID  NAME    USERNAME    PASSWORD    DEPARTMENT

and

admin table
ID  NAME    USERNAME    PASSWORD    ACCESS  CODE

and I want to query both

[craygo]

I guess it should. I never really tried it. LOL just make sure you differentiate the 2 if the field names are the same

[code]$sql = "SELECT user.name as uname, admin.name as aname FROM user, admin WHERE user.area = 1 and admin.area = 1";[/code]

Can try that. Never really tried it though. Seems a little redundant to me.


Ray

[craygo]

if the names are the same you can always add a field to the admin table called usr_id and then pull the user id from the user table and populate the admin table.

Ray

[CanMan2004]

Thanks for the help, ive used

[code]SELECT * FROM admin, users WHERE admin.id = '1' AND admin.id = '1'[/code]

Which returns 2 rows with the ID 1

The problem I have is im trying to do the where statement for a code in a field called MYID, so I want to use

[code]SELECT * FROM admin, users WHERE admin.myid = 'f65hb' AND admin.myid = '9ijh55'[/code]

But it wont return anything using that? Is there a reason it returns rows when using the ID number field, but with the MYID field, it wont return anything.

Any ideas?

[swatisonee]

But how come the two myid values are different in the 2nd code?

[CanMan2004]

Sorry that was a mistake, the ones I want to query all have the same code f65hb

[craygo]

For future reference, if you want to get multiple values from a table use
[code]SELECT * FROM admin, users WHERE admin.myid IN ('f65hb', '9ijh55')";[/code]

Ray

[CanMan2004]

Thanks Ray, i'll store that.

Do you have any ideas on my problem?

[swatisonee]

i assume MYID exists in both tables with correct case and defined as int right ?

[CanMan2004]

Hi all

Yes, MYID exists in both tables.

Can anyone help me with this as im totally stuck.

[swatisonee]

Try

[code]$sql = " SELECT * FROM admin, users WHERE (admin.myid = 'f65hb' AND admin.myid = '9ijh55') ";
echo $sql;

$result = mysql_query($sql) or die (mysql_error());[/code]and post the result

[corbin]

SELECT * FROM admin,users WHERE users.username = 'corbin' and admin.id = users.id;

This is basically the same as
$q = mysql_query("SELECT * FROM users WHERE username = 'corbin');
$r = mysql_fetch_assoc($q);
$id = $r['ID'];
$q2 = mysql_query("SELECT * FROM admin WHERE ID = '{$id}'");

Only problem is, this assumed that the ids match in both tables... You could always pull it from both tables based on USERNAME though such as...

SELECT * FROM admin,users WHERE users.username = 'corbin' and admin.username = 'corbin';