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 actually doing so I'm struggeling with any implementation. They are using JOIN statements, but wouldn't this mix entries from Table1 and Table2 together while only checking matching IDs in both tables? At least that is how I understand this http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

As you may have noticed from the style, I'm very new to using databases in general, so please forgive me if not everything is clear about what I want. Please leave a comment and I will try to improve the question if neccessary.

Answer 1

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)

Answer 2

The way joins work is not by "mixing" the data, but sort of combining them based on the key. In your case (I am assuming the key field in Table 1 is unique), if you join those two tables on the primary key field, you will end up with all the entries in table2 plus all corresponding fields from table1. If you were doing this:

select * from table1, table2 where table1.fieldID=table2.foreignkey;

then, providing your key fields are set up right, you will end up with the following:

 PointID ForeignKey(fileid) P_Property1 P_Property2 FileID F_Property1 F_Property2

The field values from table1 would be from matching rows.

Now, if you do this:

 select table1.* from table 1, table2 where
table1.fieldID=table2.foreignkey and F_Property1>1;

Would essentially get the same set of records, but will only show the columns from the second table, and only those that satisfy the where condition for the first one. Hope this helps :)