SQL query help?

[parkers]

Hi,

Currently I use perl / MySQL for web reporting for our Parts and Suppliers database ... I've been having difficulty implementing the following using an SQL SELECT statement only ... instead resorting to perl to refine the SELECT results ...

Parts - Supplier Database as follows (relevent tables and attributes shown only)

Supplier
--------
SuppNo
SuppName
SuppAddress

SuppParts
---------
SuppNo
PartNo
Qty

Now, I wish to create a web report which displays a suppliers details who do not supply a particular part ...

e.g.
SELECT SuppNo, SuppName, SuppAdress FROM Supplier, SuppParts
WHERE SuppParts.pNo != 'Part1' AND Supplier.SuppNo = SuppParts.SuppNo;

now, this statement is OK for instances where 'Part1' is not supplied by a particular Supplier however it does not produce the result I'm ideally looking for.

Can anyone provide any hints or tips on how I can proceed?
is it possible on SQL?

Thanks,
SP

 

[hos2]

SELECT SuppNo, SuppName, SuppAdress FROM Supplier LEFT OUTER JOIN SuppParts ON Supplier.SuppNo = SuppParts.SuppNo
WHERE SuppParts.pNo is NULL

 

[parkers]

Hi,

Thanks for the quick reply ... unfortunately this doesn't quite work ...

I'll explain a bit more with sample data ...

Supplier
--------

SuppNo | SuppName |
---------------------
1 | supplier 1 |
2 | supplier 2 |
3 | supplier 3 |

SuppParts
---------

SuppNo | PartNo |
--------------------
1 | Part1 |
1 | Part2 |
2 | Part2 |
2 | Part3 |
3 | Part2 |
3 | Part3 |
3 | Part4 |


Now ... I want to return all suppliers who e.g. do not or have not supplied 'Part1' ... in this example Supplier Number 2 and 3 should be returned.

Will a sub-query with Aggregate function resolve?

 

[r937]

hos2, your query wold return suppliers who supply no parts whatsoever

parkers, what you have to do is "look for" the part in the left outer join, i.e. make it a necessary join condition, and then the suppliers you want are the ones that have no match

[tt]select S.SuppNo
, S.SuppName
from Supplier S
left outer
join SuppParts SP
on S.SuppNo = SP.SuppNo
and SP.Partno = 'Part1'
where SP.Suppno is null [/tt]



rudy

http://r937.com/

SQL Consulting