I have two tables, Foo and Bar. Foo contains a foreign key to Bar’s primary key (bar_id). Bar is structured to allow a parent/child relationship to itself through a foreign key (bar_parent_id) to another record in Bar. This relationship is limited such that any Bar record that has a parent cannot itself be a parent. However, any given parent can have multiple children.
My query needs to select all of the records in Foo that match a given record in Bar as well any of the Bar’s parents, children or siblings. The query below works, but is somewhat slow. Is there any way to structure it so that it will run faster?
SELECT f.field1, f.field2 FROM Foo f WHERE f.bar_id IN ( SELECT bar_id FROM Bar WHERE bar_id = @bar_id OR bar_parent_id = @bar_id OR bar_id = (SELECT bar_parent_id FROM Bar WHERE bar_id = @bar_id) OR bar_parent_id = (SELECT bar_parent_id FROM Bar WHERE bar_id = @bar_id AND bar_parent_id > 0) )
P.S. This is a simplified version of the real query. It actually has a second, identical subquery to another table that has the same self/parent/child relationship as Bar.
You can try but I’m not sure of the correctness
SELECT f.field1, f.field2 FROM foo f LEFT JOIN bar b LEFT JOIN bar bParent ON b.parent_id = bParent.bar_id LEFT JOIN bar bChildren ON b.bar_id = bChildren.Parent_id WHERE b.bar_id = @bar_id or bParent.bar_id = @bar_id or bChildren.bar_id = @bar_id