By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,968 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,968 IT Pros & Developers. It's quick & easy.

Extracting information from two different tables ..

P: 1
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 :(
Dec 29 '15 #1
Share this Question
Share on Google+
3 Replies


zmbd
Expert Mod 5K+
P: 5,380
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.
Dec 29 '15 #2

100+
P: 122
That's right. You should have only one table contains the login credentials.
Jan 1 '16 #3

P: 8
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.
May 4 '16 #4

Post your reply

Sign in to post your reply or Sign up for a free account.