MySQL Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed ForumsDatabasesMySQL Help

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old June 2nd, 2000, 12:42 PM
fromdc fromdc is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2000
Posts: 2 fromdc User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Hey Guys,

Im using MySQL, and what I want to do is show only the first occurence records with distinct id values. Let me give an example, if I had the following table,

>create Table products_categories (
> product_id int ....,
> category_id int ...,
> )

I have two other tables, "products" and "categories" and since a single
product can belong to multiple categories, I get a side effect when
displaying all products, in that a single product can be displayed multiple
times (according to how many different categories it belongs to). For
example, if product "Apple-Orange juice", which has a product_id 1, belongs
to Apples category(category_id=3) and Oranges category(category_id=5), this
will result in two records in the products_categories table, looking like

"1,3" and "1,5"

Now when I do a select that needs to gather category, products and
products_categories fields, I get multiple records with the same
product_id, so I end up displaying the same product multiple times, when
instead I only want to display it once. So thus I want to get only one
occurence each of each distinct product_id. Now, Im solving this problem
right now via a php script, but was wondering if there was an SQL(or MySQL)
solution to this problem?

Thanks everyone,

Christian


Reply With Quote
  #2  
Old June 5th, 2000, 04:38 PM
Sepodati's Avatar
Sepodati Sepodati is offline
Banned (not really)
Dev Shed God 20th Plane (14500 - 14999 posts)
 
Join Date: Dec 1999
Location: Brussels, Belgium
Posts: 14,628 Sepodati User rank is General 51st Grade (Above 100000 Reputation Level)Sepodati User rank is General 51st Grade (Above 100000 Reputation Level)Sepodati User rank is General 51st Grade (Above 100000 Reputation Level)Sepodati User rank is General 51st Grade (Above 100000 Reputation Level)Sepodati User rank is General 51st Grade (Above 100000 Reputation Level)Sepodati User rank is General 51st Grade (Above 100000 Reputation Level)Sepodati User rank is General 51st Grade (Above 100000 Reputation Level)Sepodati User rank is General 51st Grade (Above 100000 Reputation Level)Sepodati User rank is General 51st Grade (Above 100000 Reputation Level)Sepodati User rank is General 51st Grade (Above 100000 Reputation Level)Sepodati User rank is General 51st Grade (Above 100000 Reputation Level)Sepodati User rank is General 51st Grade (Above 100000 Reputation Level)Sepodati User rank is General 51st Grade (Above 100000 Reputation Level)Sepodati User rank is General 51st Grade (Above 100000 Reputation Level)Sepodati User rank is General 51st Grade (Above 100000 Reputation Level)Sepodati User rank is General 51st Grade (Above 100000 Reputation Level)  Folding Points: 97169 Folding Title: Advanced FolderFolding Points: 97169 Folding Title: Advanced FolderFolding Points: 97169 Folding Title: Advanced FolderFolding Points: 97169 Folding Title: Advanced FolderFolding Points: 97169 Folding Title: Advanced Folder
Time spent in forums: 3 Months 6 Days 2 h 39 m 6 sec
Reputation Power: 4375
Send a message via ICQ to Sepodati Send a message via Yahoo to Sepodati
Can you put all of this into one table? I think it would make things easier. If you had a table like this:

create table products_categories (product text, prod_id int, cat_id int);

You would have values like this:
Apple-Orange Juice,1,3
Apple-Orange Juice,1,5
Apple Juice,2,3
Orange Juice,3,5

Then if you wanted to get all of the products without duplicates that matched a certain category or product id, then you would use:

SELECT DISTINCT product FROM products_categories WHERE prod_id='1';

or

SELECT DISTINCT product FROM products_categories WHERE cat_id='5';

or, for PHP,

SELECT DISTINCT product FROM products_categories WHERE prod_id='$your_var';

Hope this helps at least a little, if not, it's my $0.02..

---John Holmes
---www.SepodatiCreations.com

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesMySQL Help > A MySQL/PHP Question

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap