CODEWITHSUNDEEP
sqlms-accessjoin
Robert MacLean
Robert MacLean

Reputation: 39261

SQL Sub-Query vs Join Confusion

I have a database which is in Access (you can get it link text). If I run

SELECT DISTINCT Spl.Spl_No, Spl.Spl_Name
FROM Spl INNER JOIN Del 
   ON Spl.Spl_No = Del.Spl_No
WHERE Del.Item_Name <> 'Compass'

It provides the names of the suppliers that have never delivered a compass. However you can supposedly do this with a sub-query. So far myself and a few others have not been able to get it right.

I did come close with the following, until we added more suppliers then it stopped working

SELECT SPL.SPL_Name
FROM SPL
LEFT JOIN DEL ON Del.SPL_No = SPL.SPL_No
WHERE (DEL.Item_Name<>"Compass") OR (DEL.Item_Name IS NULL)
GROUP BY SPL.SPL_Name
HAVING COUNT(DEL.SPL_No) = 0

So the question: Is this possible to do with a sub-query.

Upvotes: 2

Views: 2427

Answers (7)

Fionnuala
Fionnuala

Reputation: 91366

I think I would go for:

SELECT SELECT Spl_No, Spl_Name
FROM Spl
WHERE Spl_No NOT IN 
  (SELECT Spl_No FROM Del
   WHERE Item_Name = 'Compass')

Upvotes: 3

Charles Bretana
Charles Bretana

Reputation: 146499

It's pretty close to exactly how you would say it in English

"Give me the suppliers who have not made a delivery of Compasses." 

Select [Stuff]
From Spl S
Where Not Exists
   (Select * From Del
    Where Spl_no = S.Spl_no 
       And Item_name  = 'Compass')

EDIT: Without Exists, you can use Count(*) = 0

Select [Stuff]
From Spl S
Where 
   (Select Count(*) From Del
    Where Spl_no = S.Spl_no 
       And Item_name  = 'Compass') = 0

Upvotes: 1

WIDBA
WIDBA

Reputation: 169

If Access has Exists.....

SELECT SPL.SPL_Name FROM SPL WHERE NOT EXISTS (SELECT 1 FROM DEL WHERE Del.SPL_No = SPL.SPL_No AND (DEL.Item_Name='Compass') )

Upvotes: 1

Dan Sydner
Dan Sydner

Reputation: 448

Do you mean something like this?

SELECT SPL.SPL_Name
FROM SPL
WHERE NOT SPL.SPL_no IN 
 (SELECT SPL_no FROM DEL WHERE DEL.Item_Name = "Compass")

Upvotes: 2

Matt Rogish
Matt Rogish

Reputation: 24873

SELECT Spl_No
     , Spl_Name
  FROM Spl
 WHERE NOT EXISTS( SELECT *
                     FROM Del
                    WHERE Del.Spl_no = Spl.Spl_no
                      AND Item_name  = 'Compass' )

Upvotes: 1

Brian
Brian

Reputation: 5966

Is this what you're looking for?

SELECT
   SPL.SPL_name
FROM
   SPL
WHERE
   SPL.SPL_No NOT IN
   (
      SELECT SPL_No FROM DEL WHERE DEL.Item_Name <> "Compass"
   )

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415860

Suppliers that have never delivered a compass:

SELECT Spl.Spl_No, Spl.Spl_Name
FROM Spl
LEFT JOIN Del ON Del.Spl_No = Spl.Spl_No AND Del.Item_Name = 'Compass'
WHERE Del.Item_Name IS NULL

and using a sub query:

SELECT Spl_No, Spl_Name
FROM Spl
WHERE Spl_No IN 
  (
    SELECT Spl_No
    FROM Del
    GROUP BY Spl_No, Item_Name
    WHERE Item_Name = 'Compass'
    HAVING COUNT(*) = 0
  )

Upvotes: 0

Related Questions