Conflict with Select Distinct statement

Guys,
I have storefront recordset. I want to assign each product to several different categories. However when I use the following code, the product shows up 3 times in one category listing:
___________
sqlQry = "SELECT * FROM qInventoryByGroup WHERE itemname LIKE 'fsgfx%' ORDER BY "
If sortby = "saleitems" Then
If how = "asc" Then
sqlQry = sqlQry & "ONSALE ASC, MANUF ASC, ITEMNAME ASC;"
arrow1 = "<img src=""img/sort-asc.gif"" width=8 height=7 alt=""Now sorted by: Sale Items, Ascending"" hspace=2 border=0 align=middle>"
___________

So i inserted ye old Select Distinct Itemname, which should solve my problem, however it causes the following error:

Microsoft OLE DB Provider for ODBC Driverserror '80040e07'
[Microsoft][ODBC Microsoft Access Driver] ORDER BY clause (MANUF) conflicts with DISTINCT.


I don't understand why the order by should conflict with the select distinct, I am only specifying to select distinct within the 'Itemname' column.. help!

Get distinct itemnames.

Then you could probably do this

Get everything associated with those distinct itemnames.

Well Memnoch that seemed to fix the error, but I still have the problem that when I assign a record to more than one category, this select statement shows the same product duplicated, once for each category.

My storefront is set up to have products in several categories at once, but for some reason this select statement is not picking out the actual distinct record.

Be more specific about how the products are assigned, and how you want them returned.

Ok,



This is how the records are currently displayed, in the paged recordset. For some reason if i assign a file to more than one category, it shows up more than once on this screen.

I am selecting the files for this page by the wildcard LIKE statement 'LIKE 'fsgfx%': fsgfx is the root inventory name for graphic files.

In addition to this inventory naming convention, files are also organized by a 'group' field in the database, i.e., what you see in the dropdownbox on the top of the screen. Flyer layouts, illustrations, etc.

What I am intending to do is add some more 'groups' to further organize the files by musical genre.. which is fine, I -can- add the groups and assign records to several categories, but the problem is that the URL above (using the SELECT statement i provided earlier) does not recognize that an individual record should not be displayed 3 times in this page just because it has 3 categories. It should only show up once.

Thanks

It seems that you should be selecting the Category, then get all of the items in that category. Instead of selecting and item and and returning it's categories...

Ok, I tried it the other way around, but still the same result; when I assign a product to more than one category, i get dupes.

sqlQry = "SELECT * FROM qInventoryByGroup WHERE ItemName IN (SELECT DISTINCT ItemName FROM qInventoryByGroup WHERE GroupName LIKE 'Graphics%') ORDER BY "



Here is the qinventorybygroup SQL if it helps:
SELECT TBLINVENTORY.ITEMID, TBLINVENTORY.SKU, TBLINVENTORY.ITEMNAME, TBLINVENTORY.DESCRIP, TBLINVENTORY.ONSALE, TBLINVENTORY.SALEDISCOUNT, TBLINVENTORY.COST, tblInv_Units.UNIT, TBLINVENTORY.SHOWIT, TBLMANUFACTURERS.NAME AS ManufName, TBLGROUPS.NAME AS GroupName, tblInventoryGroups.GroupID, TBLINVENTORY.MANUF, TBLINVENTORY.IMG_SMALL AS Thumbnail
FROM TBLMANUFACTURERS, TBLINVENTORY, tblInv_Units, TBLGROUPS INNER JOIN tblInventoryGroups ON (TBLGROUPS.GROUPID = tblInventoryGroups.GroupID) AND (TBLGROUPS.GROUPID = tblInventoryGroups.GroupID)
WHERE (((TBLINVENTORY.SHOWIT)=-1) AND ((TBLINVENTORY.MANUF)=[tblManufacturers].[ManufID]) AND ((TBLINVENTORY.UNIT)=[tblInv_Units].[ID]) AND ((tblInventoryGroups.ItemID)=[tblInventory].[ItemID]))
ORDER BY TBLMANUFACTURERS.NAME;

If your database is access, try zipping it and uploading it and I will take a look at it...

Or just post the table structure of qInventoryByGroup (assuming this is the only relevant table) and some sample (duplicated) data.

I've attached two snapshots of the db.. the table view is minus the columns which i'm sure don't impact this statement.

Memnoch, I hate to quote myself but you seem to be the man with the answers. Can you have a quick look at the screenshotsI attached in the previous message.

Thanks.

Hi Guys,

Did you solve this problem?

I have exactly the same problem here with a simular database setup.

Here is my SQL:

SELECT *
FROM Products
WHERE ProductName IN (
SELECT DISTINCT Productname
FROM Products)
ORDER BY Ordered_count DESC;

It still does not return a distinct product name, please help!

Regards,

Techy

Try doing a DISTINCT on the outer part of the query as well.

Tried that, still not joy

Regards

Tim

OK after looking at this, you might try something a little different. It looks to me like your code should just be:

SELECT * FROM Products

The (SELECT DISTINCT Productname FROM Products) will return ALL of the product names in the product table (one of each name). So if there are 100 different names of different products, that portion of the query alone would yield 100 results, so you would get 100 results for every product in the table.

If you decide which product name(s) you are looking for you could say for example:
SELECT *
FROM
Products
WHERE
Productname in ('Widgets','Wigwams','Screwdrivers')

Therefore start by selecting the distinct product names. Get just the names you want and insert them into the parentheses as I have done above, separating each with a comma and enclosing the names in a single quote ('). This only works if you are looking for products with a specific name, however.

I hope that helps!

Hi,

Thanks for your reply. I will have a think about what you have said and have another bash with it all tonight.


I just thought I would explain exactly what I am trying to do here. It is very similar to kila's problem above.


I have got a table full of products:


I.e.

Product id =1 ProductName = "Widget 1" ProductCatID = 1

Product id =2 ProductName = "Widget 1" ProductCatID = 2

Product id =3 ProductName = "Widget 1" ProductCatID = 4

Product id =3 ProductName = "A Widget" ProductCatID = 5

Product id =3 ProductName = "BigWidget" ProductCatID = 6

Product id =3 ProductName = "LittleWidget" ProductCatID = 4



What I am after is only returning the table details for unique product names. So we would only have "Widget 1" once

Regards,

Tim