Returning column indicating row existence in another table
I have two tables, Table1 and Table2.
Both of these tables have a column called Name.
I would like to query Table2 and return the Name column. In addition, I would like to return a column containing the values 1 or 0 depending on whether any rows in Table1 also contain the same value for Name.
What is the most efficient way to do this?
I'm looking for something like:
SELECT Name, IF EXISTS (SELECT * FROM Table1 WHERE Table1.Name = Table2.Name) 1 ELSE 0 FROM Table2
Sorry, I'm not very familiar with SQLite, but this should work for you. The trick is a Left Outer Join and comparison on the joined table.
SELECT Table2.Name, CASE WHEN Table1.Name IS NOT NULL THEN 1 ELSE 0 END FROM Table2 LEFT OUTER JOIN Table1 ON Table1.Name = Table2.Name
I'm not so sure the CASE syntax is the best but if you are more familiar with conditionals in SQLite you can clean that part up.