selecting rows with id from another table

Question

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?

Answer 1

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" 

Answer 2

You can use a subquery:

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

and if you need to show all columns from both tables:

SELECT t.*, tr.* FROM terms t, terms_relation tr WHERE t.id = tr.term_id AND tr.taxonomy='categ'