sql - Select from one table matching criteria in another?

Question

I'd really appreciate some help with an SQL query across tables. I realise this sort of thing is asked constantly, but I can't find a similar enough question to make sense of the answers.

I want to select rows from table_A that have a corresponding tag in table_B.
So, for example, " select rows from table_a which are tagged 'chair' " would return table_C.

Also, id is a unique in table_a, and not in table_b.

table_A:             table_B:                  table_C:

id    object         id    tag                 id    object
1     lamp           1     furniture           3     stool
2     table          2     furniture           4     bench
3     stool          3     furniture
4     bench          4     furniture
                     4     chair
                     3     chair

Alternatively, is there a better way to organise the data?

Answer 1

The simplest solution would be a correlated sub select:

select
    A.*
from
    table_A A
where
    A.id in (
        select B.id from table_B B where B.tag = 'chair'
)

Alternatively you could join the tables and filter the rows you want:

select
    A.*
from
    table_A A
inner join table_B B
    on A.id = B.id
where
    B.tag = 'chair'

You should profile both and see which is faster on your dataset.