CODEWITHSUNDEEP
sql
user3786519
user3786519

Reputation: 21

Compare subquery results

I'm stuck on what to do next. I need to compose a query that tells me if a suppliers located in a specific city both ship a common part. I have created the following query

select sh1.partno,sh1.supplierno
from shipments sh1 
where sh1.supplierno IN (
  select s.supplierno
  from suppliers s
  where s.city = 'LONDON')

This returns

partno  supplierno
  P1    S1
  P1    S1
  P3    S1
  P3    S1
  P3    S1
  P6    S4
  P2    S4
  P5    S4
  P5    S4

I'm stuck on what to do next. Here are all the parts shipped by suppliers in the specified city. How do you compare these two columns so that you want to know if a common part number is shipped by the two different supplier numbers listed in the supplierno column. I know that this will have not results, but I still need to know how to move forward.

Thanks in advance

Upvotes: 0

Views: 79

Answers (3)

Dan Bracuk
Dan Bracuk

Reputation: 20804

A self join might be what you want.

select yourfields
from shipments sh1 join shipments sh2 on sh1.partno = sh2.partno
and sh1.supplierid <> sh2.supplierid
etc

Edit

Same query without join keyword

select yourfields
from shipments sh1, shipments sh2 
where sh1.partno = sh2.partno
and sh1.supplierid <> sh2.supplierid
etc

Upvotes: 1

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

The number of suppliers in London:

select count(distinct s.supplierno)
from suppliers s
where s.city = 'LONDON'

The number of suppliers in London per partno:

select sh1.partno, count(distinct sh1.supplierno)
from shipments sh1 
where sh1.supplierno IN (
    select s.supplierno
    from suppliers s
    where s.city = 'LONDON'
)
group by sh1.partno

Which partno have as many suppliers in London as there are suppliers in London? Hint, having will simplify what remains

Upvotes: 0

AHiggins
AHiggins

Reputation: 7219

If you start with a basic list of suppliers and parts (filtered by city), your initial query will be:

SELECT sh1.partno,sh1.supplierno
FROM 
    shipments sh1 
     INNER JOIN 
    suppliers s ON 
        sh1.supplierno = s.supplierno
WHERE s.city = 'LONDON'

This gives you a list of suppliers and parts. Not being able to use a JOIN rather limits you - I wouldn't really consider an appropriately filtered self-join a Cartesian product, but hey, it's his class.

You can use the GROUP BY and HAVING clause to get just a list of parts if you want it, such as with the following:

SELECT sh1.partno, COUNT(DISTINCT sh1.supplierno) AS NumberOfSuppliers
FROM 
    shipments sh1 
     INNER JOIN 
    suppliers s ON 
        sh1.supplierno = s.supplierno
WHERE s.city = 'LONDON'
GROUP BY sh1.partno
HAVING MIN(sh1.supplierno) <> MAX(sh1.supplierno)

If, on the other hand, you're looking for the part and the supplier, you need to marry your suppliers list clause to another list of suppliers and parts. JOIN would make the most sense, but if that's off-limits the EXISTS keyword might help you here - something like the following might do want you are looking for (can't remember off the top of my head if this is right or not, so let me know if it works).

SELECT sh1.partno, sh1.supplierno
FROM 
    shipments sh1 
     INNER JOIN 
    suppliers s ON 
        sh1.supplierno = s.supplierno
WHERE 
    s.city = 'LONDON' AND
    EXISTS 
      (
        SELECT sh2.supplierno
        FROM 
            shipments sh2
             INNER JOIN 
            suppliers s2 ON 
                sh2.supplierno = s2.supplierno
        WHERE 
            s2.City = 'LONDON' AND 
            sh2.supplierno != sh1.supplierno AND
            sh2.partno = sh1.partno
      )

I would recommend the self-join suggested by @Dan Bracuk over any of these, however.

Upvotes: 0

Related Questions