CODEWITHSUNDEEP
sqlms-access
TheReddsable
TheReddsable

Reputation: 67

SQL Access QUERY to show duplicate

I have the below table in SQL Access and I want to find out which products have 2 distinct categories.

For example the product abc has only one category, so I don't want it to show up in my query, but product def has both categories, so I want it to show up.

+----+---------+----------+
| ID | Product | Category |
+----+---------+----------+
|  1 | abc     | A        |
|  2 | abc     | A        |
|  3 | def     | B        |
|  4 | def     | A        |
|  5 | abc     | A        |
+----+---------+----------+

Upvotes: 1

Views: 53

Answers (2)

Lee Mac
Lee Mac

Reputation: 16015

The answer ultimately depends on whether you are looking for products which are assigned to more than one category, or exactly 2 categories as you state in your question:

I want to find out which products have 2 distinct categories.

For the latter, you might use something like the following:

select t.product
from (select distinct product, category from YourTable) t
group by t.product
having count(*) = 2

For the former, there are many possible options - you can simply change the equality operator = in the above query to a greater than or equal to operator >= yielding:

select t.product
from (select distinct product, category from YourTable) t
group by t.product
having count(*) >= 2

Or you could use a where exists clause to test whether there exists at least one other record for the same product assigned to a different category:

select distinct t.product 
from YourTable t
where exists 
(select 1 from YourTable u where u.product = t.product and u.category <> t.category)

Or you could use aggregation with a min/max test within the having clause, as per query suggested by @forpas.

In all of the above examples, change YourTable to the name of your table.

Upvotes: 2

forpas
forpas

Reputation: 164089

Access does not support COUNT(DISTINCT ...) so for your sample data a HAVING clause where you set the condition that the minimum Category is different than the maximum Category will do:

select Product
from tablename
group by Product
having min(Category) <> max(Category)

Upvotes: 1

Related Questions