Reputation: 397
I have two tables in my database regarding the login details for both of my users (Librarians and Students) I have separated the user's details into 2 separate tables tblUserLibrarian and tblUserStudent; one for the Librarians and one for the Students. The Primary key in each table is the username or ID (LibrarianID and StudentID).
Outline of the tables are like this:
tblUserStudent
StudentID Password FirstName LastName
S1201235 pass3 Jane Smith
S1201289 pass5 Pass Word
S1202009 pass2 John Smith
tblUserLibrarian
LibrarianID Password
L1094565 pass4
L1202836 password123
L1202908 qwerty
L1212345 pass3
I have a problem where when either user tries to login using their username (which is their ID). I want to check if their username exists on either table and return all the fields from the table on which the username exists. Currently I can only think of one way which is to do 2 queries to check this however I feel that this can be done in one query. I've looked up JOINS and UNIONS but everything I've tried doesn't seem to work.
My most recent query was:
SELECT TOP 1 * FROM tblUserStudent,tblUserLibrarian
WHERE StudentID = "S1202836" OR LibrarianID = "S1202836"
But this returns rows from both tables, I just want to return the details of the user from one table if the username they entered exists.
I am using MS Access 2010 to do my queries.
Upvotes: 1
Views: 6970
Reputation: 21677
Your tables don't have the same structure. You could do a UNION ALL to do the query on both tables, but only return some information for Librarians:
SELECT TOP 1 *
FROM(
SELECT studentId AS userID, password, firstName, LastName
FROM tblUserStudent
WHERE StudentID = 'S1201235'
UNION ALL
SELECT LibrarianID,password, NULL, NULL
FROM tblUserLibrarian
WHERE LibrarianID = 'S1201235'
) a
sqlfiddle demo (sql server, but serves as an example)
I added an alias to the id's column to show you userID instead of studentID, since UNION takes the column names from the first SELECT.
I also left the TOP 1, but if your ID's are unique, you should receive only one, making it irrelevant
Upvotes: 2
Reputation: 9819
select StudentID,Password,FirstName,LastName from tblUserStudent where studentID='S1202836'
union
select LibrarianID,Password,null,null from tblUserLibrarian where LibrarianID='L1202836'
I'm not sure if i understand your requirement correctly, but it seems you want a union, not a join, and since your librarian table has fewer columns than your user student table, you have to fill up with null columns so the column count matches.
Upvotes: 0