Mysql – Select only rows that equal x but not x and y

hierarchyinnodbMySQL

I'm having quite the challenge with selecting hierarchical data in MySQL although I feel the schema should be optimized for it since it's based on the WordPress database model.

I've got 4 tables:

name

+---------+---------------+
| name_id |     name      |
+---------+---------------+
| 1       |     apple     |
+---------+---------------+
| 2       |     orange    |
+---------+---------------+
| 3       |     grape     |
+---------+---------------+
| 4       |     banana    |
+---------+---------------+

name_data

+---------+---------+---------------+
| data_id | name_id |     type      |
+---------+---------+---------------+
| 1       | 1       |   category    |
+---------+---------+---------------+
| 2       | 2       |   category    |
+---------+---------+---------------+
| 3       | 3       |   category    |
+---------+---------+---------------+
| 4       | 4       |   category    |
+---------+---------+---------------+

name_relationships

+---------+---------+-----------+------------+
| rel_id  | data_id | object_id | data_level |
+---------+---------+-----------+------------+
| 1       | 2       | 10        | 0          |
+---------+---------+-----------+------------+
| 2       | 2       | 11        | 0          |
+---------+---------+-----------+------------+
| 3       | 2       | 12        | 0          |
+---------+---------+-----------+------------+
| 4       | 4       | 12        | 1          |
+---------+---------+-----------+------------+

objects

+---------+---------------+
|object_id|     object    |
+---------+---------------+
| 10      |     object1   |
+---------+---------------+
| 11      |     object2   |
+---------+---------------+
| 12      |     object3   |
+---------+---------------+
| 13      |     object4   |
+---------+---------------+

I hope this example isn't over simplified…But I need to be able to select the rows where object_id occurs n number of times without doing a COUNT. The reason I can't use COUNT is because this is an innodb engine, and that will cause a table scan.

So, based on the data above, let's say I only need rows with name_relationships.data_id 2 (name_relationships.object_id 10 & 11) but not rows with name_relationships.data_id 2 and 4. This should prevent the rows with name_relationships.object_id 12 from being selected. But if I base the query on name_relationships.data_id = 2 it still selects name_relationships.object_id 12 because it matches one of the two rows.

Sample query:

SELECT *
FROM objects o
LEFT JOIN name_relationships a ON a.object_id = o.object_id
LEFT JOIN name_data b ON b.data_id = a.data_id
LEFT JOIN name c ON c.name_id = b.name_id
WHERE c.name = 'orange' AND a.data_level IN(0) AND a.data_level NOT IN(1)

I hope this makes sense but if not let me know and I can elaborate. Here is an environment with sample data.

EDIT :
Suppose that orange is a base category, and grape, banana is a subcategory of orange. in this example orange will have many subcategories but I only need to select objects that are related to orange but not grape or banana. Or I may need to select orange and grape but not banana.
This is what I need to do.

EDIT #2:

I am now using this query, and it works exactly like I want but it's too slow. I also added the column meta_count to posts to add anther condition.

SELECT posts.post_id,posts.post_name,posts.title,posts.type,
GROUP_CONCAT(IF(meta_data.type = 'category', meta.meta_name,null)) AS category,
GROUP_CONCAT(IF(meta_data.type = 'tag', meta.meta_name,null)) AS tag
FROM posts
LEFT JOIN meta_relationships ON meta_relationships.object_id = posts.post_id
LEFT JOIN meta_data ON meta_relationships.meta_data_id = meta_data.meta_data_id
LEFT JOIN meta ON meta_data.meta_id = meta.meta_id
WHERE meta_relationships.meta_order IN (1,2) AND posts.meta_count = 2
GROUP BY meta_relationships.object_id
HAVING category = 'animals,birds'

Best Answer

The question is not very clear. If what you want is to have as result **every object that is related to name 'orange' but not to name 'banana', here is one way:

SELECT o.*                  -- Change this to only the columns needed.
FROM objects AS o           -- If there are data needed from other tables,
                            -- join here.
WHERE EXISTS 
      ( SELECT 1 
        FROM name_relationships AS nr
          JOIN name_data AS nd USING (data_id)
          JOIN name AS n  USING (name_id)
        WHERE nc.name = 'orange' 
          AND n.object_id = o.object_id
      )
  AND NOT EXISTS 
      ( SELECT 1 
        FROM name_relationships AS nr
          JOIN name_data AS nd USING (data_id)
          JOIN name AS n  USING (name_id)
        WHERE n.name = 'banana' 
          AND nr.object_id = o.object_id
      ) ;
Related Question