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

User · Answer

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  lt col name gt    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

User · Answer

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

User · Answer

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

User · Answer

select a id  a object from table A a inner join table B b on a id b id where b tag    chair

[sql] Select from one table matching criteria in another?

Examples related to sql

Examples related to multiple-tables