SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2008
    Location
    Gloucestershire, UK
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Returning ONLY relevant categories

    Hello,

    I'm putting together a product filter system for an online shop where users can filter by brand and material, amongst other things. The options are in <select> dropdowns, and they currently reference separate tables in the database and these (if selected) and add to the query limiting which records are returned. What I was hoping to do is only display brands/materials which will return any results. I've played with an inner join but this returns the same brand/material as many times as it matches with products, which is not much use.

    To give an idea of the schema, for this part of the filter, I'll be using the following tables:

    products
    brand
    material
    associations

    The associations table contains the primary keys for each record so product->brand->material->.

    What I want to avoid is, for example the material glass appearing for sofas in the dropdown, so I could just limit it to leather, fabric etc.

    I hope this makes sense, any help will be greatly appreciated.

    Cheers,

    Jon

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jonpugh114 View Post
    I've played with an inner join but this returns the same brand/material as many times as it matches with products, which is not much use.
    SELECT DISTINCT to remove duplicate rows.

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2008
    Location
    Gloucestershire, UK
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nice...

    Thanks for that.

    Cheers,

    Jon

  4. #4
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,200
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    SELECT DISTINCT to remove duplicate rows.
    Alternatively we can use GROUP BY


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •