Query Query?

[Keith Ward]

Hi,

I am trying to write a query to display categories that have products in
them by a specific supplier.

Tables:

Categories
Products
ProductsinCategories (because products can be in multiple categories)
Suppliers


The products table has an ID field to link to the supplier ID
Products in Categories has ProductID and CategoryID

Now when I do the following query it provides the correct information but I
just want the one category to show even if it appears more than once.

SELECT Categories.CategoryID, Categories.CategoryName,
Suppliers.SupplierPrefix
FROM Suppliers INNER JOIN (Products INNER JOIN (Categories INNER JOIN
ProductsInCategories ON Categories.CategoryID =
ProductsInCategories.CategoryID) ON Products.ProductID =
ProductsInCategories.ProductID) ON Suppliers.SupplierID =
Products.SupplierID
WHERE (((Suppliers.SupplierPrefix) Like "MA"));


Gives something like

1 Category1 SUP1
1 Category1 SUP1
2 Category2 SUP1

Telling me that supplier 1 has two products in category1 and one in
category2.

What I want is just for it to list what categories the supplier has products
in so I get

Category1
Category2

Basically just to remove the duplicates for display purposes.

Hope that's clear.

Regards

Keith

 

[Keith Ward]

Its OK,

Found the SELECT DISTINCT.

Will look harder next time.

Keith

 

[Keith Ward]

OK its not quite sorted.

It works fine when I have a value for the where statement but when I put in
a default wildcard to display all the records its not producing a distinct
list of categories.

I want the distinct to only work on the category ID or name.

SQL so far:

SELECT DISTINCT Categories.CategoryID, Categories.CategoryName,
Suppliers.SupplierPrefix
FROM Suppliers INNER JOIN (Products INNER JOIN (Categories INNER JOIN
ProductsInCategories ON Categories.CategoryID =
ProductsInCategories.CategoryID) ON Products.ProductID =
ProductsInCategories.ProductID) ON Suppliers.SupplierID =
Products.SupplierID
WHERE (((Suppliers.SupplierPrefix) Like "*"))

Regards

Keith

 

[John Vinson]

OK its not quite sorted.

It works fine when I have a value for the where statement but when I put in
a default wildcard to display all the records its not producing a distinct
list of categories.

I want the distinct to only work on the category ID or name.

SQL so far:

SELECT DISTINCT Categories.CategoryID, Categories.CategoryName,
Suppliers.SupplierPrefix
FROM Suppliers INNER JOIN (Products INNER JOIN (Categories INNER JOIN
ProductsInCategories ON Categories.CategoryID =
ProductsInCategories.CategoryID) ON Products.ProductID =
ProductsInCategories.ProductID) ON Suppliers.SupplierID =
Products.SupplierID
WHERE (((Suppliers.SupplierPrefix) Like "*"))

The SELECT DISTINCT groups over all the fields included in the SELECT
lcause. If you don't want to group by the SupplierPrefix just remove
it from the SELECT clause (and leave it in the WHERE clause).
Something like

SELECT DISTINCT Categories.CategoryID, Categories.CategoryName
FROM Suppliers INNER JOIN (Products INNER JOIN (Categories INNER JOIN
ProductsInCategories ON Categories.CategoryID =
ProductsInCategories.CategoryID) ON Products.ProductID =
ProductsInCategories.ProductID) ON Suppliers.SupplierID =
Products.SupplierID
WHERE (((Suppliers.SupplierPrefix) Like [Enter supplier, or leave
blank for all:] & "*"))


John W. Vinson[MVP]