View Full Version : Joining 2 tables

03-19-2013, 09:23 AM
Hi all,

I have a problem in joining 2 tables. each tables have different fields and only 3 fields that are same.

1st table (user)
name, position, class, username and password

2nd table (student)
name, course, supervisor, username and password

How to join this two tables? I want to do joining for this tables for log in purpose.

03-19-2013, 03:46 PM
field name is name of what, user, course, ... ?

Old Pedant
03-19-2013, 09:07 PM
You don't JOIN them. You UNION them.

$sql = "SELECT name, position, `class`, NULL AS course, NULL AS supervisor "
. " FROM user "
. " WHERE username = '$usr' AND password = '$pwd' "
. " UNION "
. " SELECT name, NULL, NULL, course, supervisor "
. " FROM student "
. " WHERE username = '$usr' AND password = '$pwd' ";

Old Pedant
03-20-2013, 05:10 AM
You donít join user and student table. You can use two tables separately. So, open your databases create two new tables and use it separately.

What a nonsense answer, especially after I gave him a *correct* way to do what he seems to be after.

He wants a *SINGLE* login form, where the user enters name and password, and then he wants to search both tables for possible matches on name and password and log the person in automatically according to what table he/she is in. My code allows him to do that.

03-20-2013, 04:11 PM
Thanks for the reply.. Now, I added more fields to my Student's table but User's table remain the same.

Student's table: name, reg_id, address, contact_number, edu_level, email,course, supervisor, username and password

User's table: name, position, class, username and password

My question is, how to union these table since the number of columns are not same.

Thanks for the help!

Old Pedant
03-21-2013, 12:53 AM
Do as I showed in my first answer:

Any columns that you want to get from table A that are not in table B, you simply use (just making ONE example) NULL AS edu_level

Note that, in a UNION, *ONLY* the field names given in the *FIRST* SELECT are seen by your PHP code.

So if you did (just another example!)

SELECT studentid, studentName FROM students WHERE ...
SELECT userid, username FROM users WHERE ...

Then in your PHP code (or any other code, for that matter), you would *NOT* be able to do something like $row["userid"] or $row["username"]. Again, ONLY the field names from the first SELECT are visible, so it's very important the you use meaningful names in *that* SELECT, even when supplying just NULL values.