#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    2
    Rep 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

  2. #2
  3. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,640
    Rep Power
    4476
    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

Similar Threads

  1. [NEW USERS] - How to post a question.
    By JeffCT in forum PHP Development
    Replies: 39
    Last Post: December 2nd, 2010, 08:13 AM
  2. PHP data retrieval question with MySQL
    By gursharn in forum PHP Development
    Replies: 7
    Last Post: December 31st, 2003, 09:43 AM
  3. Javascript question mark question?
    By tajmiester in forum JavaScript Development
    Replies: 3
    Last Post: November 30th, 2003, 05:36 PM
  4. Radio Buttons
    By Ulysses Freeman in forum HTML Programming
    Replies: 4
    Last Post: November 15th, 2003, 09:57 PM
  5. Associative array question - newbie
    By square in forum PHP Development
    Replies: 7
    Last Post: January 25th, 2002, 11:29 AM

IMN logo majestic logo threadwatch logo seochat tools logo