patternsqlMinor

SELECT other rows based on one matching row

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

rowsonebasedselectotherrowmatching

Problem

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?

Solution

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...

Code Snippets

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;

Context

StackExchange Database Administrators Q#138927, answer score: 2

Revisions (0)

No revisions yet.