SQL - Check if data exists on either table

Question

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.

No, they only exist in either table not both. The 'S1202836' should be a variable and the query should be interpreted as 'Check if the username exists in either table not both' — Nubcake, Dec 08 '13 at 20:05
does `SELECT whatever FROM table1, table2 WHERE ...` actually work?! — oldboy, Apr 01 '18 at 22:17

score 2 · Accepted Answer · answered Dec 08 '13 at 19:55

2

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

answered Dec 08 '13 at 19:55

Filipe Silva

21,189
5
53
68

This query seems to work thanks. I added TOP 1 because I thought it would only return 1 row, my ID's are unique. What does the 'a' at the end of the FROM mean ? – Nubcake Dec 08 '13 at 20:03
1

It is an alias for the query inside the first FROM (). Whenever you use a SELECT statement like this inside FROM, it is treated as a table, but it has no table name. That a acts as its table name. – Filipe Silva Dec 08 '13 at 20:06
Ah I had seen similiar syntax like this in other queries and wondered what that was for. Thanks again for clearing that up. – Nubcake Dec 08 '13 at 20:10
You can use alias for every table, but in most cases you do it only to not write the table names a lot of times. but in this case it is mandatory. – Filipe Silva Dec 08 '13 at 20:11
Just to clear this up, the query inside the FROM() will return a table so that the first SELECT can get all the info from it? – Nubcake Dec 08 '13 at 20:14
Yes. that is correct. A table with one element. But as you said that you will only get one result, you can remove the outer query completely. It was there only to do the TOP 1. – Filipe Silva Dec 08 '13 at 20:17
What happens if I don't include the alias ? The 'a' , the query seems to work fine but there's more to it ? – Nubcake Dec 08 '13 at 20:19
You did my exact query in access without the 'a' and it worked? – Filipe Silva Dec 08 '13 at 20:23
I removed the TOP 1 and did it. – Nubcake Dec 08 '13 at 20:24
Ah. i was understanding it wrong. That is what i was advising you to do. You can remove the top because you only get one result from that query. See [here](http://sqlfiddle.com/#!3/2f747/11) for the two queries working equaly – Filipe Silva Dec 08 '13 at 20:27
Ok , So I don't need to an alias on the end then. – Nubcake Dec 08 '13 at 20:28
Nop. not in that version. – Filipe Silva Dec 08 '13 at 20:29

score 0 · Answer 2 · answered Dec 08 '13 at 19:52

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.

SQL - Check if data exists on either table

2 Answers2

Linked