selecting rows with id from another table

database, mysql, sql

Solution

Try this (subquery):

SELECT * FROM terms WHERE id IN 
   (SELECT term_id FROM terms_relation WHERE taxonomy = "categ")

Or you can try this (JOIN):

SELECT t.* FROM terms AS t 
   INNER JOIN terms_relation AS tr 
   ON t.id = tr.term_id AND tr.taxonomy = "categ"

If you want to receive all fields from two tables:

SELECT t.id, t.name, t.slug, tr.description, tr.created_at, tr.updated_at 
  FROM terms AS t 
   INNER JOIN terms_relation AS tr 
   ON t.id = tr.term_id AND tr.taxonomy = "categ"

Problem

Let's call this table `terms_relation`: ``` +---------+----------+-------------+------------+------------+--+ | term_id | taxonomy | description | created_at | updated_at | | +---------+----------+-------------+------------+------------+--+ | 1 | categ | non | 3434343434 | 34343433 | | | 2 | categ | non | 3434343434 | 3434343434 | | | 3 | tag | non | 3434343434 | 3434343434 | | | 4 | tag | non | 3434343434 | 3434343434 | | +---------+----------+-------------+------------+------------+--+ ``` And this is table `terms`: ``` +----+-------------+-------------+ | id | name | slug | +----+-------------+-------------+ | 1 | hello | hello | | 2 | how are you | how-are-you | | 3 | tutorial | tutorial | | 4 | the end | the-end | +----+-------------+-------------+ ``` How Do I select all rows in table `terms` and table `terms_relation` where it's taxonomy in table `terms_relation` is `categ`? Will I need two queries for this or I could use a `join` statement?

Original source