How to search multiple tables at the same time in sqlite3?

user_12818651 0
Viewed 119

I'm tying to design a Sign In page. I have three tables students, professors, dean. I want to fetch the password for the given username and check if it's correct. I can do this for one table with :

def get_password_by_username(username):
    c.execute("SELECT password FROM students WHERE username = :username", {"username": username})
    return c.fetchone()

How can I search for the username in all three tables at the same time? thanks.

sqlite python
3 Answers

This query:

SELECT username, password FROM students 
UNION ALL
SELECT username, password FROM professors
UNION ALL
SELECT username, password FROM dean

returns all the usernames and passwords from the 3 tables and you can filter to get the password like this:

SELECT password
FROM (
  SELECT username, password FROM students 
  UNION ALL
  SELECT username, password FROM professors
  UNION ALL
  SELECT username, password FROM dean
)
WHERE username = :username
user_104988280

If you want to get all the data associated with some student based on student's username, you should use JOIN in your SQL queries to achieve that. You will maybe have to redesign your database a little bit to be able to get all the data you need.

There are plenty of nice guides around about how to use JOIN (this one or this one), read them to understand better how to join your tables in a desired way.

Very simple example:

SELECT 
    *
FROM 
    students s
LEFT JOIN professors p 
    ON s.professorID = p.professorID
WHERE
    s.username = 'some_username';
user_1757930

You can separate them with a comma

def get_password_by_username(username):
    c.execute("SELECT password FROM students, professors, dean WHERE username=?", (username,)
    return c.fetchone()
user_139628910
Related