Exclude records from results if other records referencing them are linked to a specific value

Question

Here's what I'm trying to accomplish:

There are four tables I have to reference to get the data I am looking for. list, part, listitem, and labelassign.

• The list table has records representing each list
• The listitem table references list.id and represents all of the items on each list
• The part table holds all parts and each part has a unique id
• The labelassign table has friendly labels (like tags) where a partId is referenced in order to label parts.

Visual:

List

id  name
--  ----
1   part1
2   part2
3   part3
4   part4

Listitem

id  partId  listId
--  ----    ------
1   10      1
2   11      1
3   12      1
4   13      2
5   14      2

Part

id  name
--  ----
1   part1
2   part2
3   part3
4   part4
10  part10
11  part11
12  part12
13  part13
14  part14

Labelassign

id  label          partId
--  -----          ----
1   StandardParts  1
2   StandardParts  2
3   SmallParts     3
4   LargeParts     4
5   HugeParts      5
6   MediumParts    10
7   MediumParts    11
8   MediumParts    12
9   SmallParts     13
10  MediumParts    14

To get all lists with a particular label:

SELECT list.name
FROM list
INNER JOIN part ON list.name = part.name
INNER JOIN labelassign ON part.id = labelassign.partId AND labelassign.label LIKE '%StandardParts%'
# Using LIKE for '%StandardParts%' because there are variations in the real data but I want them all

If the list.name records do not have the StandardParts label, they should be excluded from results.

The second part is more tricky and where I have trouble.

Each list record has a number of listitem records that represent the items on that list. All list records which have the StandardParts label should have at least one listitem record with a SmallParts label, but not all do and the ones that don't are what I want to find.

listitem records are known as belonging to a particular list by their listitem.listId field. So, for each list I want to check if any of its listitems have SmallParts as a label (by way of checking the matching part, and that part's label as shown above), and if none of those listitems do I want the parent list record kept in the result set. Otherwise, the list.name should be excluded from the results.

Based on the initial answer from @Eric Brandt, this is what I have so far:

SELECT DISTINCT
    l.id,
    l.num
FROM
    list AS l
INNER JOIN
    part AS p1
        ON b.num = p1.num
# Without this up here I didn't get the filtration, lists without the StandardParts label were included in results
INNER JOIN
    labelassign AS la
        ON p1.id = la.partId
        AND la.label LIKE '%StandardParts%'
INNER JOIN
    listitem AS li
        ON l.id = li.listId
INNER JOIN
    part AS p
        ON li.partId = p.id
WHERE
    EXISTS
        (
            SELECT 1
            FROM
                labelassign AS la1
            WHERE
                la1.partId = p.id AND
                la1.label LIKE '%StandardParts%'
        )
    AND NOT EXISTS
        (
            SELECT 1
            FROM
                labelassign AS la2
            WHERE
                la2.partId = p.id AND
                la2.label LIKE '%SmallParts%'
        );

The above still returns list records which have listitem records which have the proper label of SmallParts. Again, the goal is to filter those out because I needn't fix those. I'm looking for all list records which don't have a listitem record with a label of SmallParts.

Expected Results

id  name
--  ----
1   part1

Only list.id 1 should be returned because it has a label of StandardParts and none of its listitems have a label of small parts. list.id 2 does have a label of StandardParts, but one of its listitems has a label of SmallParts so it should be excluded.

This question touches upon my issue but is mostly Java oriented and the answers do not answer my question.

This question again brushes by my issue but is really a different problem, that of a non-existent value in another table rather than exclusion based on the presence of a value.

I'm happy to provide more information if I left something out.

Answer 1

You want a « LEFT JOIN » on items having « SmallParts », with a WHERE clause that filters out matching rows. You can use « SELECT DISTINCT » to avoid duplicates. Finally, I suspect that you do not need to JOIN on table « part », I removed it.

SELECT DISTINCT 
    list.id, 
    list.name
FROM 
    list
    INNER JOIN listitem ON list.id = listitem.listId
    INNER JOIN labelassign l1 ON listitem.partId = l1.partId AND l1.labelId =  AND l1.label LIKE '%StandardParts%'
    LEFT JOIN labelassign l2 ON l2.partId = listitem.partId AND l2.label LIKE '%SmallParts%';
WHERE l2.partId IS NULL

Answer 2

I would try an EXISTS clause to find list.ids that have a StandardParts label, and then a NOT EXISTS clause to limit those results to the lists where no parts have the SmallParts label.

SELECT 
  l.id, 
  l.name
FROM 
  list AS l
INNER JOIN 
  listitem AS li
    ON l.id = li.listId
INNER JOIN 
  part AS p
    ON li.partId = p.id
WHERE 
  EXISTS
    (
      SELECT 1
      FROM 
        labelAssign AS la1 
      WHERE 
        la1.partId = p.id AND
        la1.label LIKE '%StandardParts%'
    )
  AND NOT EXISTS
    (
      SELECT 1
      FROM 
        labelAssign AS la1 
      WHERE 
        la1.partId = p.id AND 
        la1.label LIKE '%SmallParts%'
    );