CODEWITHSUNDEEP
sqlsqlite
theactiveactor
theactiveactor

Reputation: 7554

Querying with foreign key

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

Upvotes: 1

Views: 120

Answers (3)

Rob
Rob

Reputation: 5603

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'

Upvotes: 1

user352353
user352353

Reputation:

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.

Upvotes: 1

spender
spender

Reputation: 120450

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'

Upvotes: 3

Related Questions