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

User · Answer

SELECT terms   FROM terms JOIN terms relation ON id term id WHERE taxonomy  categ

User · Answer

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

User · Answer

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

[mysql] selecting rows with id from another table

Examples related to mysql

Examples related to sql

Examples related to database