I have SHIPMENT Table like below.

select ShipperNo,Parts from SHIPMENT

I need to find all the Shippers that ship ALL the parts shipped by S2.

select Parts from SHIPMENT where ShipperNo='S2'

would be

I am not sure how to either do an intersection between the above two subqueries and get the ShipperNo. I am not necessarily a DBA who uses SQL on a day to day basis and this question stumped me in the interview, I am more of a Full stack Developer with basic SQL knowledge.

Edit1:I forgot to mention the interviewer asked me to do this without using the count.

CodePudding user response：

Count for each shipper the number of parts it has in commin with S2 and then filter on the ones who have exactly the same number of parts as S2:

select SH1.ShipperNo, count(*) partsCount
  from shipment SH1
 where SH1.Parts in (select SH2.Parts from SHIPMENT SH2 WHERE SH2.ShipperNo = 'S2')
 group by SH1.ShipperNo
having count(*) = (select count(*) from SHIPMENT SH3 WHERE SH3.ShipperNo = 'S2')

CodePudding user response：

You can use a self-join with a subquery:

Edit: solution without count function, per the details in the question:

with sub_s2(id, c) as (
   select s.shipperno, sum(s.shipperno != s1.shipperno and s1.shipperno = 'S2') from shipment s 
   join shipment s1 on s.parts = s1.parts 
   group by s.shipperno
)
select s.id shipperno from sub_s2 s where s.c = (select sum(s3.shipperno = 'S2') from shipment s3)

Output:

shipperno
---------
S1