CODEWITHSUNDEEP
mysqlsqldatabaserelational-database
Rami
Rami

Reputation: 61

I am trying to do a mysql query to get the suppliers who only supply a red part?

Here is mysql Query 

select distinct C.sid
 from Catalog C  
where not exists 
( select * from Parts P where P.pid = C.pid and P.color != 'red');

but it return the suppliers who supply red parts not ONLY red parts ? and ideas how to change that

I have 3 tables

Suppliers(sid: integer, sname: string, address: string) 
Parts(pid: integer, pname: string, color: string) 
Catalog(sid: integer, pid: integer, cost: real)

Any help will be appreciated , thanks

Upvotes: 1

Views: 2021

Answers (2)

Laurence
Laurence

Reputation: 10976

select
    sid
from
    suppliers s
where
    exists (
        select 
            'x'
        from
            catalog c
                inner join
            parts p
                on c.pid = p.pid
        where
            s.sid = c.sid and
            p.color = 'red'
    ) and
    not exists (
        select
            'x'
        from
            catalog c
                inner join
            parts p
                on c.pid = p.pid
        where
            s.sid = c.sid and
            p.color != 'red'
    )

Upvotes: 1

T I
T I

Reputation: 9943

Try

Select c.id
From catalog c
Where exists (
     Select 1 From Parts p
     Where p.pid = c.pid And p.color = 'red')
And not exists (
     Select 1 From Parts p
     Where p.pid = c.pid And p.color <> 'red')

Upvotes: 0

Related Questions