Sids of supplier who supply some particular part using Nested Queries

Question

Schema:

Suppliers(sid: integer, sname: string, address: string)

Parts(pid: integer, pname: string, color: string)

_Catalog(sid: integer, pid: integer, cost: real)

And the question is:

Find the sids of suppliers who supply some red part or are at 221 Packer Ave.

I've tried different methods like:

Method 1:

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

Method 2:

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

It shows no output in the 2nd method and returns an error in the 1st method Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. What am i doing wrong? and Is there any other, solution to this?

NOTE: I need a nested query to solve this problem, because I've not studied joins and advance stuff yet.

if you are using sqlserver .. why tag mysql ??????? – ScaisEdge Mar 16 '17 at 16:43 — ScaisEdge, Mar 16 '17 at 16:43
@scaisEdge Thanks for correcting me :) – Ahsan Mar 16 '17 at 16:45 — Ahsan, Mar 16 '17 at 16:45

score 1 · Accepted Answer · answered Mar 16 '17 at 18:51

1

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'));

answered Mar 16 '17 at 18:51

Ahsan

412
5
17

ScaisEdge · Answer 2 · 2017-03-16T16:55:49.917

0

If you can manage more than a value you can use IN clause

select sid 
from   Suppliers 
where  sid IN   (select sid
              from parts  p 
              inner join _Catalog c c.pid= p.pid
              where p.color= 'Red') 
or     address='221 Packer Ave';

you could use an aggregation function eg: max

select sid 
from   Suppliers 
where  sid = (select max(sid)
              from parts  p 
              inner join _Catalog c c.pid= p.pid
              where p.color= 'Red') 
or     address='221 Packer Ave';

or you can limit the number or rows selected eg: TOP 1

edited Mar 16 '17 at 16:55

answered Mar 16 '17 at 16:46

ScaisEdge

131,976
10
91
107

1

The answer is wrong, WHERE sid IN (SELECT pid...) is invalid, supplier ID and product ID are unrelated... – Philip P. Mar 16 '17 at 16:48
@PhilP. thanks ... answer update with relation – ScaisEdge Mar 16 '17 at 16:56

score 0 · Answer 3 · answered Mar 16 '17 at 16:47

0

It happens because:

select pid 
from parts 
where color= 'Red'

returns more than one pid, and you are using = on your where condition.

Use IN or limit the returned pid to one row.

select top (1) pid 
from parts 
where color= 'Red'

answered Mar 16 '17 at 16:47

McNets

10,352
3
32
61

there can be more than 1 suppliers. – Ahsan Mar 16 '17 at 16:54

Philip P. · Answer 4 · 2017-03-16T16:56:43.763

The best way to solve the problem would be a JOIN instead of derived queries. Here is an example:

SELECT DISTINCT
    sup.sid
FROM
    Suppliers AS sup
    LEFT OUTER JOIN _Catalog cat ON sup.sid = cat.sid
    INNER JOIN Parts par ON cat.pid = par.pid
WHERE
    sup.address = '221 Packer Ave'
    OR cat.color = 'Red'

What you seem to be missing is the fact that to determine the supplier of a part you need data from all three tables: parts have the color, catalog has the relationship between the supplier and the part and finally the supplier has the SID.

Another solution would be a CTE, which sometimes might perform better (due to different query plan and especially the fact that DISTINCT is not required) but works only with SQL Server 2008R2+:

;WITH CTE_RedPartSuppliers AS (
    SELECT
        cat.sid
    FROM
        Parts par
        INNER JOIN _Catalog cat ON par.pid = cat.pid
    WHERE
        par.color = 'Red'
)

SELECT
    sup.sid
FROM
    Suppliers sup
WHERE
    address = '221 Packer Ave'
    OR EXISTS (
        SELECT 1 FROM CTE_RedPartSuppliers rps WHERE rps.sid = sup.sid
    )

score 0 · Answer 5 · answered Mar 16 '17 at 16:54

0

Curious why you cant just do an inner join?

select parts.sid 
from   Suppliers Suppliers inner join parts parts
where  
parts.color = 'Red' 
or     
parts.address = '221 Packer Ave'

answered Mar 16 '17 at 16:54

CoSpringsGuy

1,615
1
13
16

I'm just a beginner, haven't studied joins yet :') – Ahsan Mar 16 '17 at 16:59

Sids of supplier who supply some particular part using Nested Queries

5 Answers5