CODEWITHSUNDEEP
sqlms-access
Roël Gonzalez
Roël Gonzalez

Reputation: 72

SQL Display based on count of other table

We have the following Tables:

**Product**
Productnr


**Deliverable**
Deliverernr
Productnr

**Deliverer**
Deliverernr

The relationship between Product and Deliverer is a Many to Many and the Deliverable is its junction table

I am trying to display all the products that are only delivered by ONE deliverer

I have tried the following with no success:

SELECT Product.Productnr
FROM Product, Deliverable, Deliverer
WHERE Product.Productnr = Deliverable.Productnr AND
Deliverer.Deliverernr = Deliverable.Deliverernr
HAVING COUNT(Product.Productnr) = 1;

I am using MS access

Thanks in advance for any help.

Upvotes: 0

Views: 39

Answers (1)

sunny
sunny

Reputation: 833

I don't know why you need to use all tables when you have one junction point and you want specific result that depends both variables of junction point, I may be wrong in getting your question. Try this if it helps:

SELECT Productnr FROM Deliverable GROUP BY Productnr HAVING COUNT (Deliverernr)=1

Upvotes: 1

Related Questions