Select from one table matching criteria in another?

Solution 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.

Solution 2:

You should make tags their own table with a linking table.

items:
id    object
1     lamp  
2     table   
3     stool  
4     bench 

tags:
id     tag
1      furniture
2      chair

items_tags:
item_id tag_id
1       1
2       1
3       1
4       1
3       2
4       2

Solution 3:

select a.id, a.object
from table_A a
inner join table_B b on a.id=b.id
where b.tag = 'chair';

Solution 4:

I have a similar problem (at least I think it is similar). In one of the replies here the solution is as follows:

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

That WHERE clause I would like to be:

WHERE B.tag = A.<col_name>

or, in my specific case:

WHERE B.val BETWEEN A.val1 AND A.val2

More detailed:

Table A carries status information of a fleet of equipment. Each status record carries with it a start and stop time of that status. Table B carries regularly recorded, timestamped data about the equipment, which I want to extract for the duration of the period indicated in table A.