CODEWITHSUNDEEP
sql
user3577756
user3577756

Reputation: 643

How to remove all entries of a number conditionally in SQL

Consider the following schema: Suppliers (sid: integer, sname: string, address: string) , Parts (pid: integer, pname: string, color: string) , and Catalog (sid: integer, pid: integer, cost: real)

The Catalog relation lists the prices charged for parts by suppliers.

Find the sids of suppliers who supply only red parts.

So I know I need to eventually select the catalog sid for the supplier but I don't know how to remove all instances of a supplier sid if they sell and item that isn't colored red.

Would appreciate any direction. I have been trying to do combinations of AND but can't seem to eliminate all sids that sell parts not red.

Upvotes: 0

Views: 130

Answers (1)

Charles Bretana
Charles Bretana

Reputation: 146559

Suppliers who sell only red parts:

Select distinct c.Sid
From Catalog c join Parts p 
   on p.pid = c.pid
Where p.Color = 'RED'
   and Not exists
        (Select * from Catalog 
         Where sid = c.Sid
            and pid in (Select Pid from Parts 
                        Where Color != 'RED'))

In English, it's pretty much exactly what you stated in the question

"Find the sids of suppliers who supply only red parts."

Which, converting the word only into it's two parts.... is equivalent to

"Find the sids of suppliers who supply red parts and do not supply any parts that are not red."

so.... 

Select distinct c.Sid   -- "Find the sids  of suppliers ..."
From Catalog c join Parts p  
   on p.pid = c.pid
Where p.Color = 'RED'   -- "who supply red parts ... "
   and Not exists       -- " and do not supply ..." 
        (Select * from Catalog -- "  Parts ... "
         Where sid = c.Sid       
            and pid in (Select Pid from Parts  -- " ... That are ..."
                        Where Color != 'RED')) -- " ... Not Red"

Upvotes: 1

Related Questions