How to exclude rows where matching join is in an SQL tree

Answer 1

You can achieve that with a single query:

SELECT
    i.item_id,
    COUNT(DISTINCT(i.attr_id)) AS attributes 
FROM
    items i
INNER JOIN
    attributes a
        ON i.attr_id = a.attr_id
WHERE
    a.parent_id = 0
GROUP BY
    i.item_id
HAVING
    i.labels > 1

Answer 2

Well it seems it 's not possible with one query since we have nothing to group by, or nothing to sort on. The one thing left will be to do some recursive call but since there are no recursive SQL in mysql or if your attribute data have a rule where for all linked attribute attr_id < parent_id.

Answer 3

The following query will extract only unique pairs of item and attribute (or its parent if any), thus eliminating the duplicates (this is per your request that an attribute can have only one parent, and the parent has no parents).

SELECT DISTINCT I.item_id AS iid, A.par_id AS aid
FROM 
    items AS I, 
    (SELECT AA.attr_id, IF(AA.parent_id = 0, AA.attr_id, AA.parent_id) AS par_id 
     FROM attribute AS AA) AS A
WHERE I.attr_id = A.attr_id
ORDER BY I.item_id

So, using the above query as a subtable for your counting query will work (same approach I used with the A subtable above):

SELECT SUB.iid, COUNT(DISTINCT(SUB.aid)) AS attributes
FROM
    (SELECT DISTINCT I.item_id AS iid, A.par_id AS aid
     FROM 
        items AS I, 
        (SELECT AA.attr_id, IF(AA.parent_id = 0, AA.attr_id, AA.parent_id) AS par_id 
         FROM attribute AS AA) AS A
     WHERE I.attr_id = A.attr_id
     ORDER BY I.item_id) AS SUB
GROUP BY SUB.iid
HAVING attributes > 1

I have added 3 more rows to your example items table, to accommodate the case, where an item can be linked only to an attribute with parent, but not the parent itself (i.e. item 3 -> 23 and 3 -> 20), and 4 -> 23.

Running the above query lists only items 2 and 3 with 2 attributes each.

Answer 4

To simplify this, I updated all rows in item with the parent attribute ID, where one is available.

So in my example item table, with the attribute IDs updated it looks like:

+------------+------------+-----------+
| item_id    | name       | attr_id   |
+------------+------------+-----------+
| 1          | BMW 320d   | 21        |
| 1          | BMW 320d   | 21        |
| 2          | BMW 335i   | 23        |
| 2          | BMW 335i   | 34        |
+------------+------------+-----------+

First I got a list of attribute relations (child-to-parent):

SELECT a.attr_id, a.parent_id FROM item i JOIN attribute a 
USING (attr_id) WHERE parent_id > 0 GROUP BY a.attr_id

I looped around this in code and updated the rows in item that referenced a child attribute.

$update = array();

foreach ($relations as $child => $parent) {
    if (!isset($update[$parent]))
        $update[$parent] = array();

    $update[$parent][] = $child;
}

Loop around $update to update item. With this done I was able to use my original query:

SELECT item_id, COUNT(DISTINCT(attr_id)) AS attributes 
FROM item GROUP BY item_id HAVING attributes > 1

I couldn't get one query to work.