Select values from one table depending on referenced value in another table

Question

I have two tables in my SQLite Database (dummy names): Table 1: FileID F_Property1 F_Property2 ... Table 2: PointID ForeignKey(fileid) P_Property1 P_Property2 ... The entries in Table2 all have a foreign key column that references an entry in `Table1`. I now would like to select entries from `Table2` where for example `F_Property1` of the referenced file in `Table1` has a specific value. I tried something naive: ``` select * from Table2 where fileid=(select FileID from Table1 where F_Property1 > 1) ``` Now this actually works..kind of. It selects a correct file id from `Table1` and returns entries from `Table2` with this ID. But it only uses the first returned ID. What I need it to do is basically connect the returned IDs from the inner select by `OR` so it returns data for all the IDs. How can I do this? I think it is some kind of cross-table-query like what is asked here What is the proper syntax for a cross-table SQL query? but these answers contain no explaination of what they are…

Accepted Answer

The `=` operator compares a single value against another, so it is assumed that the subquery returns only a single row.

To check whether a (column) value is in a set of values, use IN:

```
SELECT *
FROM Table2
WHERE fileid IN (SELECT FileID
                 FROM Table1
                 WHERE F_Property1 > 1)
```

Select values from one table depending on referenced value in another table

Solution

Problem