Compare subquery results

Question

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

The syntax you need is `GROUP BY .. HAVING`. Is this homework? — Hugh Jones, Jul 16 '14 at 16:24
Something like this perhaps? http://stackoverflow.com/questions/8149210/how-do-i-find-duplicates-across-multiple-columns in your case you would select s.city, t.* from [shipments] s join ( select partno, supplierno, count(*) as qty from [shipments] group by partno, supplierno having count(*) > 1 ) t on s.partno = t.partno and s.supplierno= t.supplierno — Tim, Jul 16 '14 at 16:29
yes it is a homework assignment. I've tried group by having but doesn't that just tell me that one of the suppliers shipped a part multiple times and not that they shipped the same part as another supplier? — user3786519, Jul 16 '14 at 16:35
I should probably add that the supplier city name in this case london is a seperate table from shipments where the supplierno and partno are both listed. That's why I have the subquery for finding the supplierno. — user3786519, Jul 16 '14 at 16:40

Dan Bracuk · Answer 1 · 2014-07-16T17:57:20.847

1

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

edited Jul 16 '14 at 17:57

answered Jul 16 '14 at 16:24

Dan Bracuk

20,699
4
26
43

Thanks we are not permitted to use the join keyword unfortunate. – user3786519 Jul 16 '14 at 16:37
The instructor is very adamant that we not use anything considered or resembling a Cartesian product – user3786519 Jul 16 '14 at 16:38
OK I got that part to work from your edit. The last part is to figure out how to device the supplierno from a different table and insert this into the supplier ids. were supplier City = "LONDON' I guess my questions would be how to you make sh1.supplierid and sh2.supplierid a value from another table? – user3786519 Jul 16 '14 at 19:21
You should make that a separate question. This one is so old it is not going to get many more people looking at it. When you post it, add a tag for the database engine you are using. It may increase the number of potential answerers and will prevent people from asking you for it. Also, mention right off the bat that this is an academic exercise and include all restrictions to which you are subjected. That will increase the likelihood of you getting a good answer. – Dan Bracuk Jul 17 '14 at 01:01

score 0 · Answer 2 · answered Jul 16 '14 at 17:15

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.

Thanks based on everything I've read I would all well, in this case I'm limited by what the instructor has provided. Thanks — user3786519, Jul 16 '14 at 17:23

score 0 · Answer 3 · answered Jul 16 '14 at 17:18

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

Compare subquery results

3 Answers3