How to exclude rows where matching join is in an SQL tree
- by Greg K
Sorry for the poor title, I couldn't think how to concisely describe this problem.
I have a set of items that should have a 1-to-1 relationship with an attribute.
I have a query to return those rows where the data is wrong and this relationship has been broken (1-to-many). I'm gathering these rows to fix them and restore this 1-to-1 relationship.
This is a theoretical simplification of my actual problem but I'll post example table schema here as it was requested.
item table:
+------------+------------+-----------+
| item_id | name | attr_id |
+------------+------------+-----------+
| 1 | BMW 320d | 20 |
| 1 | BMW 320d | 21 |
| 2 | BMW 335i | 23 |
| 2 | BMW 335i | 34 |
+------------+------------+-----------+
attribute table:
+---------+-----------------+------------+
| attr_id | value | parent_id |
+---------+-----------------+------------+
| 20 | SE | 21 |
| 21 | M Sport | 0 |
| 23 | AC | 24 |
| 24 | Climate control | 0 |
....
| 34 | Leather seats | 0 |
+---------+-----------------+------------+
A simple query to return items with more than one attribute.
SELECT item_id, COUNT(DISTINCT(attr_id)) AS attributes
FROM item GROUP BY item_id HAVING attributes > 1
This gets me a result set like so:
+-----------+------------+
| item_id | attributes |
+-----------+------------+
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
-- etc. --
However, there's an exception. The attribute table can hold a tree structure, via parent links in the table. For certain rows, parent_id can hold the ID of another attribute. There's only one level to this tree. Example:
+---------+-----------------+------------+
| attr_id | value | parent_id |
+---------+-----------------+------------+
| 20 | SE | 21 |
| 21 | M Sport | 0 |
....
I do not want to retrieve items in my original query where, for a pair of associated attributes, they related like attributes 20 & 21.
I do want to retrieve items where:
the attributes have no parent
for two or more attributes they are not related (e.g. attributes 23 & 34)
Example result desired, just the item ID:
+------------+
| item_id |
+------------+
| 2 |
+------------+
How can I join against attributes from items and exclude these rows?
Do I use a temporary table or can I achieve this from a single query?
Thanks.
