Extracting information from two different tables ..

[manuhackzzz]

I am creating a login page ,

tables:
database has tables "student" and "faculty"

schema:
"student" table has columns "sid,spass,sfname.....etc."
"faculty" table has columns "fid,fpass,ffname......etc."

Problem---
I want to create a temporary table which contains all
"sid,fid" as "username" column
and all "spass,fpass" 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_addressbook
(standard address book information less member names)

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

tbl_member_address_profile
relates tbl_member_account to tbl_member_addressbook as more than one member could be residing at a given address.

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

tbl_Member_classification_profile
relates tbl_member_account to tbl_member_classifications
a member could be a student, staff, and or faculty

tbl_member_credentials
Could store a hash of the member's account/badge derived by using the account/badge, classification, salt from tbl_member_account_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.