altweb — Tue, 20 Dec 2005 17:25:25 +0000

Here are a couple options that might work for you.

One solution to get a single result is to group by the product id and use a summary action on the other fields:

SELECT products.p_id, MAX(products.p_name), MAX(products.co_id),
 MAX(cat_link_pro.c_id) FROM products INNER JOIN cat_link_pro ON products.p_id 
= cat_link_pro.p_id WHERE (products.p_name Like '%p%' OR products.p_description Like '%p%') 
AND products.co_id=13 AND cat_link_pro.c_id=24
GROUP BY products.p_id;

A second solution would use the DISTINCT action, but only select the product id and name. This would only work if you do not use the products.co_id and ink_pro.c_id fields from your results. If you do use these fields then I would go with the GROUP BY option.

SELECT DISTINCT products.p_id, products.p_name
FROM products INNER JOIN cat_link_pro ON products.p_id 
= cat_link_pro.p_id WHERE (products.p_name Like '%p%' OR products.p_description Like '%p%') 
AND products.co_id=13 AND cat_link_pro.c_id=24;

Hope this helps.

codehungry — Tue, 20 Dec 2005 04:46:01 +0000

Hi JeevesBond,

I did advise my client of the possible problems with Access but her reply was she would switch to another database if that time ever came. She was set on using MS Access; I think she was a MS Access pro. She wanted to design your own queries and reports...

Thanks,
Andrew Schools

JeevesBond — Mon, 19 Dec 2005 21:47:23 +0000

Well am glad to hear you've got the structure normalised. You will have a problem with Access though as soon as you reach more than about 10 concurrent users. You said this is a shopping cart?

Access just isn't built to cope with this kind of thing, and I'm not trying to be rude here, even Bill Gates would be tut-tutting over this one!

Good luck though and let us know how it goes.

codehungry — Mon, 19 Dec 2005 19:25:04 +0000

Hi JeevesBond,

Yes, I am using a MS ACCESS database for this project and no, it's not by choice. Originally, the application was built in PHP and MYSQL but I was forced to convert it when my client moved to a Windows Server that doesn't support PHP or MYSQL. MS-SQL is not an option either or I would use this over MS ACCESS.

In the products table, there is no data redundancy Every product is linked to a category using a composite (bridge) entity

I have thought about using a sub query but didn't give it much thought because I assumed it could be done using this method. I will give this a try and let you know...

Thanks,
Andrew Schools

JeevesBond — Mon, 19 Dec 2005 13:30:31 +0000

Hmmm, for one thing I didn't know DISTINCTROW actually worked outside of MS Access, am assuming you're using SQL Server here (and really hoping you're not trying to build a shopping cart in Access! ). You might want to check that predicate is not only included for compatibility.

Have a look at this: http://www.accessvba.com/forum/archive/index.php/t-2636.html
This relates to MS Access but should be relevant. DISTINCT works on the resultset from the query whereas DISTINCTROW works on the source tables of the query.

In your case you should create a subquery to select your products - using DISTINCT, this will only retrieve unique products, then join the subquery to category after. Only problem is that your subquery cannot contain the category field as both products will be returned.

In fact the more I think about this, the worse it sounds. Unfortunately it sounds like your database schema isn't right. How can you have two records in the products table that have exactly the same id but different categories?

I think you're trying to create a many-to-many relationship, but without the linker table in the middle. What're the primary keys for both these tables?