SQLite Select from three tables where variable matches

Question

SQL query beginner, obviously.

So I have 3 tables with data: Account, Owner, Vehicle.

These three are tied together with a common variable: "User_Name".

I want to get all items from these three tables where User_Name matches my search variable. So if i search for a user with User_Name: "Mike" all items from the three tables with the User_Name "Mike" will be displayed.

I have the following query using general "JOIN", but how can I change this to use a search variable to select ALL that match the variable from ALL tables?

    cursor.execute("SELECT * FROM Account JOIN Owner ON(Account.User_Name = Owner.User_Name) JOIN Vehicle ON(Account.User_Name = Vehicle.User_Name)")

I have looked on various other Stack-threads about "select from multiple tables" but all of them only select from 2 tables and the ones that select from 3, doesn't have the variable match that I want.

So in short:

Search three tables and get all rows where a common column variable matches a search-string.

Possible duplicate of [How can an SQL query return data from multiple tables](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) — MooingRawr, Jan 05 '17 at 16:35
Maybe the answer to this question will help: http://stackoverflow.com/questions/35470590/sql-select-of-data-from-three-tables-relationed?rq=1 — igneosaur, Jan 05 '17 at 16:53
Add `WHERE Account.User_Name = %s`, and substitute the search name. — Barmar, Jan 05 '17 at 16:57
I'd like to be able to give you points for the use of the word 'obviously'. And no this is not sarcasm. Hordes of people ask questions and somehow imagine that we wouldn't be able to tell. :) — Bill Bell, Jan 05 '17 at 17:09

score 0 · Accepted Answer · answered Jan 06 '17 at 00:01

Since your tags indicate Python, I will show the answer using Python, assuming the search_user variable holds the user name we want to search for:

cursor.execute("SELECT * FROM Account JOIN Owner ON(Account.User_Name = Owner.User_Name) JOIN Vehicle ON(Account.User_Name = Vehicle.User_Name) WHERE Account.User_Name='%s'" % search_user)

Things are more obvious if we just extract the SQL and format it in a more readable form:

SELECT * 
  FROM Account 
  JOIN Owner ON (Account.User_Name = Owner.User_Name)
  JOIN Vehicle ON (Account.User_Name = Vehicle.User_Name)
 WHERE Account.User_Name='%s'

Above query will filter all records from table Account which contain searched User_Name. The joins will match records from other tables with matching join conditions.

Note that in case you do not have User_Name that matches searched value in any of the tables, no record will be shown in the result. For example, there is a record with User_Name = 'Joe' in table Account, but there are no records for 'Joe' in table Vehicle or 'Owner'. The result will not have record for 'Joe'.

Ah, so all i needed was to add a "WHERE" clause at the end. I tried this solution before asking the question but I guess i must have gotten an incorrect syntax. This solved my problem. Thank you! — DannyBoy, Jan 06 '17 at 12:51

SQLite Select from three tables where variable matches

1 Answers1