Sql Distinct
Hi, I need some help with the following SQL code that utilizes the DISTINCT keyword:
SELECT DISTINCT products.p_id, products.p_name, products.co_id,
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;This SQL is dynamically generated from an ASP script and is used to search a list of products in a shopping cart. Everything works other than the DISTINCT keyword. For example, using the following SQL code above, it returns two of the same products because they are both in different categories. I understand this because they are both unique in the sense they both belong to different categories, however, I would assume that since I'm using the DISTINCT keyword against the products.p_id field, it would return only that one product? I would use the DISTINCTROW keyword but this wouldn't work because if the products are in a separate category, they are considered unique.
Thanks for any advise you may have!
Andrew Schools
JeevesBond posted this at 13:30 — 19th December 2005.
He has: 3,956 posts
Joined: Jun 2002
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?
a Padded Cell our articles site!
codehungry posted this at 19:25 — 19th December 2005.
He has: 10 posts
Joined: Feb 2004
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 posted this at 21:47 — 19th December 2005.
He has: 3,956 posts
Joined: Jun 2002
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.
a Padded Cell our articles site!
codehungry posted this at 04:46 — 20th December 2005.
He has: 10 posts
Joined: Feb 2004
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
altweb posted this at 17:25 — 20th December 2005.
They have: 14 posts
Joined: Nov 2005
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_nameFROM 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.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.