3

I am trying to get related rows in a tag table based on one tag, where "related" means "having the same area_id". Example:

tag       area_id
--------  -------
turkey    1
stuffing  1
carrots   1
chicken   2

I want to get rows 'stuffing' and 'carrots' based on knowing 'turkey'. How might I accomplish this with one query (and/or subqueries) please?

0

2 Answers 2

2

Probably easiest to just do a self-join:

SELECT x.tag 
FROM tagtable x
JOIN tagtable y
    ON x.area_id = y.area_id
WHERE y.tag = 'turkey'
  AND x.tag <> y.tag
2
  • Any thoughts on how this might be modified to allow for additional tags? IE: WHERE (y.tag='turkey' OR y.tag='stuffing')?
    – appl3s
    Commented May 29, 2016 at 3:21
  • That will do, if you want to avoid parenthesis you can use : y.tag in (...). Commented May 29, 2016 at 3:33
2

if i'm reading your question properly, you know there is a 'turkey' tag and want your query to return every row that shares the same area_id but with a tag that is not 'turkey'. Right? Try...

SELECT a.* FROM tagtable a,
(
SELECT * FROM tagtable WHERE tag = 'turkey'
) As z
WHERE a.area_id = z.area_id
AND a.tag <> z.tag;

hope that helps...

1
  • Thank you very much, this answer worked, though an EXPLAIN yielded a slightly less efficient query overall than @lennart's answer.
    – appl3s
    Commented May 19, 2016 at 2:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.