Querying with foreign key

Question

Say I have 2 tables whose structures are as follows:

tableA

id | A1 | A2

tableB

id | tableA_id (foreign key) | B1

Entries in A have a one-to-many relationship with entries in B. What kind of query operation would I need to achieve "something like this: select all objects from table B where A1="foo""? Basically, apply a query on tableA and from those result, find corresponding dependent objects in tableB

Answer 1

That would be best performed with a join:

select 
    B.* 
from 
    tableB as B 
    join tableA as A 
        on B.tableA_id=A.id 
where 
    A1='foo'

Answer 2

SELECT * FROM tableB WHERE tableA_id IN (SELECT id FROM tableA WHERE A1 = "foo");

Subqueries my friend.

The work fine on MySQL and Oracle. Don't know about SQL Server. Hope is what you are looking for.

Answer 3

You need to join table A and B and issue a query on the result:

select * from
tableA join tableB
ON tableA.A1 = tableB.tableA_id
WHERE tableA.A1 = 'foo'