Clever Geek Handbook

SQL Query in Access - record still appears even if it doesn't match criteria

I'm pretty sure the problem with my SQL code is something very minor, but I've been trying to run this query for hours to no avail. I have a table with three columns: Maker, Model and Type. I am trying to write a query that outputs "Manufacturers" that produce "PCs" or "Laptops" but NOT "Printers". I'll post my code below.

SELECT DISTINCT Product.maker
FROM Product
WHERE (((Product.type)="pc" Or (Product.type)="laptop")) AND ((Product.type)<>"printer");

This is a much smaller modified table, similar to the one I am working with:

maker   model   type
 A      1001    pc
 A      1002    pc
 C      1007    pc
 D      1008    pc
 E      2003    laptop
 A      2004    laptop
 E      3003    printer
 D      3005    printer
 H      3006    printer

Any help would be greatly appreciated. Thank!

+3


source to share


2 answers


you can use NOT EXISTS

for this



SELECT DISTINCT P.maker
FROM Product P
WHERE (P.type="pc") Or (P.type="laptop")
AND NOT EXISTS
( SELECT 1 FROM Product P1
  WHERE P.maker = P1.maker
  and P1.type ="printer"
)
+1


source


This is an example of a "set-in-sets" query. I like to approach these queries using aggregation and suggestion having

. In your case:

SELECT p.maker
FROM Product as p
WHERE SUM(iif(p.type in ("pc", "laptop"), 1, 0)) > 0 AND
      SUM(iif(p.type = "printer", 1, 0) = 0;


The first condition counts the number of products that are "PC" or "laptop" and requires at least one. The second counts the number of printers and requires that there are none.

This structure is quite flexible for a wide variety of conditions.

+1


source






More articles:


All Articles