1

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

theactiveactor
  • 7,314
  • 15
  • 52
  • 60

3 Answers3

3

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'
spender
  • 117,338
  • 33
  • 229
  • 351
  • 1
    +1. Further reading, "What joins does SQLite support?": http://stackoverflow.com/questions/774475/what-joins-does-sqlite-support – Marc Bollinger May 30 '10 at 00:57
  • 1
    Visual Explanation of joins: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html – OMG Ponies May 30 '10 at 01:03
1

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.

  • this works too, thanks! is there a performance difference between subquery vs. join? – theactiveactor May 30 '10 at 01:35
  • It depends on the database engine... but normally joins consume more memory because both tables have to be crossed into memory... and when you use the sub queries its more efficient because you one do one query at the time and only use a fraction of the tables and don't make a join... But some database engines like Oracle 10g improve queries optimization over joins and things like that... any way... I stoped liking inner joins a lot of years ago... In my personal experiencie if the database engine you are using supports sub queries use them instead of joins. –  May 30 '10 at 02:52
1

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'
Rob
  • 5,525
  • 1
  • 24
  • 26