Oracle/SQL - Finding records, if a similar doesnt exist, and joining on a 2nd table

I'm trying to find all the distinct 'widget' names in my color table where the widget's color is 'red', but the widget doesn't also have certain other colors... say 'yellow' or 'blue' and the 'red' record has an edition of 'S'.

widget  color   edition
-----------------------
widA    red     S
widA    green   N
widB    red     S
widB    blue    L
widC    red     S
widC    red     S
widE    black   N
widF    red     N

The query should return

widget
------
widA
widC
开发者_如何学编程

I then need to join the results with the rest of a 'widgets' data from the widget table...

widget    description     weight
----------------------------------
widA        awesome         30
widB       super neat      10
widC        amazing         100
widE        spectacular     1
widF        dyn-o-mite!     16

which would give me

widget    description     weight
----------------------------------
widA        awesome         30
widC        amazing         100

So if I'm thinking through it I need to do a self join of sorts of the color table on itself and then a innerjoin on the widget table?

The process in my head makes a lot of sense; putting it into SQL is another story.

---

Using NOT IN

SELECT w.widget 
     , w.description
     , w.weight
FROM widgets w 
  JOIN colors c
    ON w.widget = c.widget
WHERE c.color = 'red'
  AND c.edition = 'S'
  AND w.widget NOT IN 
  ( SELECT widget
    FROM colors
    WHERE color IN ('blue', yellow') 
  )

---

To ensure no duplication appears (even if there are duplicates in table: color), use DISTINCT in the above query, or:

Using IN and NOT IN

SELECT w.widget 
     , w.description
     , w.weight
FROM widgets w 
WHERE w.widget IN 
  ( SELECT widget
    FROM colors
    WHERE color IN ('red') 
      AND edition = 'S'
  )
  AND w.widget NOT IN 
  ( SELECT widget
    FROM colors
    WHERE color IN ('blue', yellow') 
  )

Using JOIN and NOT EXISTS

SELECT DISTINCT w.widget 
     , w.description
     , w.weight
FROM widgets w 
  JOIN colors c
    ON w.widget = c.widget
WHERE c.color = 'red'
  AND c.edition = 'S'
  AND NOT EXISTS 
  ( SELECT 1
    FROM colors c2
    WHERE w.widget = c2.widget
      AND c2.color IN ('blue', yellow') 
  )

Using one JOIN and GROUP BY

I don't have an Oracle now to test, but this will work too, I think (can someone verify?):

SELECT w.widget 
     , w.description
     , w.weight
FROM widgets w 
  JOIN colors c
    ON w.widget = c.widget
GROUP BY w.widget
HAVING COUNT( color = 'red' 
              AND edition = 'S' ) >= 1
   AND COUNT( color = 'blue' ) = 0  
   AND COUNT( color = 'yellow' ) = 0

Using (LEFT) self JOIN on color and one more JOIN to widgets

SELECT w.widget 
     , w.description
     , w.weight
FROM widgets w 
  JOIN 
  ( SELECT DISTINCT good.widget
    FROM colors good
      LEFT JOIN colors bad
        ON good.widget = bad.widget
          AND bad.color IN ('blue', yellow')
    WHERE good.color = 'red'
      AND good.edition = 'S'
      AND bad.widget IS NULL
  ) AS c
    ON w.widget = c.widget