Find The Sids of The Suppliers Who Supply Every Part

Find the sids of the suppliers who supply every part

This is a double nested NOT EXISTS query (no really, that's what I've usually seen it called), and it is used specificially to answer this type of quesion, i.e., "Are there any x true for all y?"

Here's MySQL's page on EXISTS and NOT EXISTS, which mentions this technique specifically.

First, in the innermost SELECT query, you are selecting parts that each store carries. Then, with the first NOT EXISTS clause, you are selecting parts that are NOT carried by each store. Finally, in the outer NOT EXISTS clause, you are selecting stores that returned an empty set for the inner NOT EXISTS clause, meaning they carry every part.

Here is a SQLFiddle of this query in action.

A word of warning: if you are working with SQL, it is always good to think and work in sets, and thinking in linear terms like what is about to follow can get you into trouble quickly. Don't make it a habit!

However, sometimes when trying to figure out a complex query like this, it can help to think of these things as loops.

So, taking the data in the fiddle as an example, we have:

suppliers:
sid, name
9, 'AAA'
8, 'BBB'
7, 'CCC'

parts:
pid, name
1, 'wood'
2, 'stone'
3, 'paper'

catalog:
cid, pid, sid
1,1,9
2,2,9
3,1,8
4,1,7
5,2,7
6,3,7

So with this data, AAA carries wood and stone, BBB only carries wood, and CCC carries wood, stone, and paper.

Now let's step through the query row by row. We are selecting from suppliers, and we are deciding which rows to include in the result set, so start with the first row in suppliers: 9,'AAA'. We'll call this row S temporarily. We'll only include this row if there is nothing in the inner result set, so lets take a look at that.

suppliers:
sid, name
S => 9, 'AAA'
     8, 'BBB'
     7, 'CCC'

This result set is selecting from parts, and we are going to go through it row by row. S still equals 9,'AAA' while we do this. So start with the first row in parts: 1,'wood'. We'll call this row P for now. We'll only include this row in this first inner result set if there is nothing in the next level of result set, so let's move there. Remember that S = 9,'AAA' and P = 1,'wood'.

suppliers:
sid, name
S => 9, 'AAA'
     8, 'BBB'
     7, 'CCC'

parts:
pid, name
P => 1, 'wood'
     2, 'stone'
     3, 'paper'

This innermost query is selecting from 'catalog'. We're looking for any row, we'll call it C, in catalog where C.sid equals S.sid AND C.pid equals P.pid. This means that the current supplier carries the part. We want parts that the current supplier DOESN'T carry, that's why we invert the result set. We know that S's sid is 9, and we know that P's pid is 1. Are there any rows in C that match that? The very first row in C matches that, so we know that this result set is not empty.

suppliers:
sid, name
S => 9, 'AAA'
     8, 'BBB'
     7, 'CCC'

parts:
pid, name
P => 1, 'wood'
     2, 'stone'
     3, 'paper'

catalog:
cid, pid, sid
C => 1,1,9 --Match found! Don't include P in outer result set
     2,2,9
     3,1,8
     4,1,7
     5,2,7
     6,3,7

Now jump back to the next outermost loop. The inner result set was not empty, so we know that 1,'wood will not be part of this loop's result set. So we move to the next row in parts, 2,'stone'. We update the value of P to equal this row. Should we include this row in the result set? We have to run the inner query again with our new value for P (S has still not changed). So we look for any rows in catalog with sid equal to 9 and pid equal to 2. The second row matches, so there is a result set.

suppliers:
sid, name
S => 9, 'AAA'
     8, 'BBB'
     7, 'CCC'

parts:
pid, name
     1, 'wood'
P => 2, 'stone'
     3, 'paper'

catalog:
cid, pid, sid
     1,1,9 
C => 2,2,9 --Match found! Don't include P in outer result set
     3,1,8
     4,1,7
     5,2,7
     6,3,7

Jump back to the next outermost loop. The inner result set was not empty, so 2,'stone' will not be part of this loop's result set.

When we go through all this again for 3,'paper', we find that there are no rows in catalog that have sid = 9 and pid = 3. The innermost result set is empty, so we know to include this value of P in the next outermost loop.

suppliers:
sid, name
S => 9, 'AAA'
     8, 'BBB'
     7, 'CCC'

parts:
pid, name
     1, 'wood'
     2, 'stone'
P => 3, 'paper'

catalog:
cid, pid, sid
     1,1,9 
     2,2,9 
     3,1,8
     4,1,7
     5,2,7
     6,3,7
C => --No match found, include P in outer result set

We have gone through the entire parts table at this point, so we have our final result set for the second loop, and it is not empty, which means that we found a part that S does not carry, so we know that we cannot include the current value of S in our final outer loop's result set.

So we move on to the next row in suppliers and start the whole process over:

suppliers:
sid, name
     9, 'AAA'
S => 8, 'BBB'
     7, 'CCC'

Once we get to S = 7,'CCC' we will go through all of these loops, and a match will be found in the inner loop for every value of P that is supplied, which means that that second loop will have an empty set. We couldn't find any parts that supplier doesn't carry, so that value of S is added to the result set, meaning they carry everything!

Find the sids of suppliers who supply only red parts

You do want aggregation. One method is to compare the colors using min() and max():

select c.SID
from cat c JOIN
     part p
     ON c.PID = p.PID
group by c.SID
having min(p.color) = max(p.color) and  -- all colors are the same
       min(p.color) = 'red'             -- and that color is 'red'

find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers who supply that part)

You could turn your subquery into a JOIN, so that its result is available in the outer query :

SELECT c.sid, c.pid, c.cost, c1.avg_cost   
FROM catalog AS c
INNER JOIN (SELECT pid, avg(cost) AS avg_cost FROM catalog GROUP BY pid) c1
    ON c1.pid = c.pid
WHERE c.cost > c1.avg_cost

PS : in your original query, you did not need a GROUP BY since, you did not use aggregate functions in the outer query. Also, based on your data it seems likely that you do not need the DISTINCT functionality.

Find the names of the suppliers who supply all the parts in MS Access

You need a CROSS join of Suppliers and Parts and a LEFT join to SPB.

Then you group by supplier and set the condition in the HAVING clause:

SELECT t.supplierID, t.name
FROM (
  SELECT s.ID AS supplierID, s.name, p.Id AS part_ID
  FROM Suppliers AS s, Parts AS p
) AS t LEFT JOIN SPB AS b ON b.Supplier = t.supplierID AND b.Part = t.part_ID
GROUP BY t.supplierID, t.name
HAVING COUNT(*) = COUNT(b.Part)

Sids of supplier who supply some particular part using Nested Queries

Just figured it out :)

select sid from Suppliers where address='221 Packer Ave' or sid IN (select sid from _Catalog where pid IN(select pid from Parts where color = 'Red'));

Find the sids of suppliers who supply a red part AND a green part

You must group by cat.sid and put the condition in the HAVING clause:

select 
  c.sid, 
  min(p.color) color1 ,
  max(p.color) color2
from cat c inner join par p 
on c.pid = p.pid
where p.color in ('green', 'red')
group by c.sid
having count(distinct p.color) = 2

After selecting only parts with the 2 colors, there will be only suppliers who supply 1 or both colors.

The condition having count(distinct p.color) = 2 returns only the suppliers who supply both colors.

min() and max() return the 2 colors. They are not really needed in this case but I preferred to use them instead of hardcoding them.

Find the Supplier number for those suppliers who supply every part

You're close. You need to add a group by/having clause with a subquery:

 group by s.sname having count(*) = (select count(*) from catalogue)

How to find the Supplier number for those suppliers who supply all parts that are supplied by another by supplier in SQL?

this is how I achieved the result:

create table MyTable (
  Supplier varchar(20),
  Part varchar(20),
  SomeOrder int
)

insert into MyTable values 
('S1', 'P1', 100),
('S1', 'P2', 200), 
('S2', 'P1', 300), 
('S2', 'P2', 100),
('S3', 'P1', 400),
('S4', 'P1', 100), 
('S4', 'P2', 200)
---------------------------

-- temporary table to store all parts of a supplier
CREATE TABLE #tmp (Part varchar(20))

-- put your desired provider here
declare @Supplier varchar(20) = 'S2'

-- select all parts of this supplier
insert into #tmp
select Part from MyTable where Supplier = @Supplier

-- count how many parts this supplier has
declare @partCount int
select @partCount = count(*) from #tmp

-- select all suppliers that have all the parts (i.e. count = @partCount)
select innerTbl.Supplier from (
    select m.Supplier, m.Part
    from MyTable m
    inner join #tmp t on t.Part = M.Part
  ) innerTbl
 group by innerTbl.Supplier
 having Count(innerTbl.Supplier) = @partCount 
 and innerTbl.Supplier <> @Supplier

DB2 SQL Find the Id of Suppliers, who supply specific color

You're doing a join already - an implicit, comma separated one.

Tip of today: Always use modern, explicit JOIN syntax. Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed!

select p.id, s.suppliers, s.color
from suppliers s
JOIN parts p ON s.num = p.num
where s.color IN ('blue', 'red')

Also use table aliases (s, p), and qualify the columns, e.g. s.num.

JOIN-free version:

select *
from
(
    select p.id,
           (select s.suppliers from suppliers s where s.num = p.num) suppliers,
           (select s.color from suppliers s where s.num = p.num) color
    from parts p
) dt
where color in ('blue', 'red');

---

Related Topics