MySQL Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
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:
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!
  #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
Dev Shed God 19th Plane (14000 - 14499 posts)
 
Join Date: Dec 1999
Location: Afghanistan
Posts: 14,378 Sepodati User rank is General 12nd Grade (Above 100000 Reputation Level)Sepodati User rank is General 12nd Grade (Above 100000 Reputation Level)Sepodati User rank is General 12nd Grade (Above 100000 Reputation Level)Sepodati User rank is General 12nd Grade (Above 100000 Reputation Level)Sepodati User rank is General 12nd Grade (Above 100000 Reputation Level)Sepodati User rank is General 12nd Grade (Above 100000 Reputation Level)Sepodati User rank is General 12nd Grade (Above 100000 Reputation Level)Sepodati User rank is General 12nd Grade (Above 100000 Reputation Level)Sepodati User rank is General 12nd Grade (Above 100000 Reputation Level)Sepodati User rank is General 12nd Grade (Above 100000 Reputation Level)Sepodati User rank is General 12nd Grade (Above 100000 Reputation Level)Sepodati User rank is General 12nd Grade (Above 100000 Reputation Level)Sepodati User rank is General 12nd Grade (Above 100000 Reputation Level)Sepodati User rank is General 12nd Grade (Above 100000 Reputation Level)Sepodati User rank is General 12nd Grade (Above 100000 Reputation Level)Sepodati User rank is General 12nd Grade (Above 100000 Reputation Level)  Folding Points: 70305 Folding Title: Intermediate FolderFolding Points: 70305 Folding Title: Intermediate FolderFolding Points: 70305 Folding Title: Intermediate FolderFolding Points: 70305 Folding Title: Intermediate Folder
Time spent in forums: 2 Months 3 Weeks 6 Days 22 h 13 m 53 sec
Reputation Power: 1784
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


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 | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 6 hosted by Hostway