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?

Chris Nava · Accepted Answer · 2011-03-27T02:26:30.420

141

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.

edited Mar 27 '11 at 02:26

answered Mar 27 '11 at 02:21

Chris Nava

6,614
3
25
31

@rjschnorenberg's suggestion is better if you have the luxury of changing the schema. In which case you would join the three tables Items<>Item_Tags<>Tags using two INNER JOIN clauses. – Chris Nava Mar 27 '11 at 02:29
Great, that's very helpful. I wasn't aware you could filter columns from the joined table like that. – Mar 27 '11 at 02:40
Actually you can also write the join line as "inner join table_B B on A.ID = B.ID AND B.tag = 'chair'" but I prefer to put filters under WHERE. – Chris Nava Mar 27 '11 at 03:32
Which of these is "better" practice and performance wise? Or is it entirely preference? – Stephen Feb 15 '18 at 16:04
That depends very much on the tables in question. In general, I use the first option if the select from B will produce a small number of records and the latter method if the number of B records is likely to be very high. However, they are similar enough that's it's not much effort to try both. – Chris Nava Apr 07 '23 at 01:50

score 8 · Answer 2 · answered Mar 27 '11 at 02:22

8

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

answered Mar 27 '11 at 02:22

rjschnorenberg

661
1
9
30

This is the better answer assuming you have control over the schema. If you don't, consider creating a view which unions the two object tables so that you can then do what this answer suggests. – Rodney P. Barbati Aug 12 '17 at 03:01

score 3 · Answer 3 · answered Mar 27 '11 at 02:21

3

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

answered Mar 27 '11 at 02:21

RichardTheKiwi

105,798
26
196
262

score 2 · Answer 4 · edited May 25 '18 at 06:27

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.

Select from one table matching criteria in another?

4 Answers4

Linked