Extracting information from two different tables ..

[manuhackzzz]

I am creating a login page ,

tables:
database has tables "student&q uot; and "faculty&q uot;

schema:
"student" table has columns "sid,spass,sfna me.....etc."
"faculty" table has columns "fid,fpass,ffna me......etc."

Problem---
I want to create a temporary table which contains all
"sid,fid" as "username" column
and all "spass,fpas s" as "password" column. thus resultant table should have only 2 columns containing username and password of all members , witch i can use further to apply validation for successful login.
So what query i have to write to extract that table ??
ps: tried join operations but cant figure it out :(

[zmbd]

AFIAK: You will have to use a UNION on either two queries or two views to achieve this; however, your schema doesn't seem to be the best.

tbl_Member_addr essbook
(standard address book information less member names)

tbl_Member_acco unt
(has the member's names and an account/badge name and a random number(salt))

tbl_member_addr ess_profile
relates tbl_member_acco unt to tbl_member_addr essbook as more than one member could be residing at a given address.

tbl_Member_clas sifications
(has the classifications available, Faculty, Staff, Student, etc..)

tbl_Member_clas sification_prof ile
relates tbl_member_acco unt to tbl_member_clas sifications
a member could be a student, staff, and or faculty

tbl_member_cred entials
Could store a hash of the member's account/badge derived by using the account/badge, classification, salt from tbl_member_acco unt_profile. There would be a second salt value here and this would be used with the entered pass phrase to create/verify the stored hash for the pass phrase.

In the long run you would have only the one table to validate any user credentials against once you've pulled the other information from the related tables.

I'm sure that there are much better schema then one I just outlined as it is quite literally a "top of my head" outline.

[adriancs]

That's right. You should have only one table contains the login credentials.

[lambertsoftware]

I would use one table for login credentials with a field like uid, then use LEFT JOIN to set uid with fid or sid as the JOIN field. EX: "fid on uid" and "sid on uid". Joins can be confusing but it's essential to learn them.