selecting rows with id from another table
Solution 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"
Solution 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'
Solution 3:
SELECT terms.*
FROM terms JOIN terms_relation ON id=term_id
WHERE taxonomy='categ'