SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting product brands within current category

    Hi there

    I was wondering if this query could be improved....

    Basically, I have a table containing a product category tree, a table of products, a table of brands and a link table relating a product to any number of products. The products table contains a brand_id as a foreign key.

    So when viewing products within a given category, I want the user to be able to filter the results by brand. So I need to select a list of brands that have products within the selected category. Here is what I have:
    Code:
    SELECT	 
    	DISTINCT(b.brand_id),
    	b.brand_name
    FROM 
    	brands AS b
    INNER JOIN 
    	products_to_categories AS ptc
    	ON ptc.category_id = $catid
    INNER JOIN
    	products AS p
    	ON p.prod_id = ptc.prod_id
    WHERE
    	b.brand_id = p.brand_id
    ORDER BY
    	b.brand_name
    Can this be made more efficient or is it an acceptable solution?

    Many thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by sxtrail View Post
    Can this be made more efficient or is it an acceptable solution?
    that's hard to say without a full understanding of your indexes and without seeing the EXPLAIN for the query

    but there are several things i'd like to point out

    first, DISTINCT is ~not~ a function -- it applies to all the columns in the SELECT clause, not just the one column that you've put parentheses around (which aren't necessary)

    second, don't mix JOIN syntax together with join conditions in the WHERE clause

    also, when you feed a parameter value into a query, it's best to feed it to the "driving" table, which should be the first table in your FROM clause, like this --
    Code:
    SELECT DISTINCT
           b.brand_id
         , b.brand_name
      FROM products_to_categories AS ptc
    INNER 
      JOIN products AS p
        ON p.prod_id = ptc.prod_id
    INNER 
      JOIN brands AS b 
        ON b.brand_id = p.brand_id
     WHERE ptc.category_id = $catid
    ORDER 
        BY b.brand_name
    writing the ptc table first in the FROM clause, and providing a value for it in the WHERE clause, means that you want all product brands for a certain category -- just read through the sequence of tables in the FROM clause and see if it doesn't make a lot more sense now

    developing a habit to strive for clarity in the FROM clause will help you down the road when you have to understand what a query is doing if you have to make changes to it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Thanks for your reply. That makes perfect sense and does read more logically. Here is the result of an EXPLAIN. Hopefully it makes sense:
    Code:
    id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
    1 	SIMPLE 	ptc 	ref 	PRIMARY,reverse_i 	reverse_i 	4 	const 	9 	Using index; Using temporary; Using filesort
    1 	SIMPLE 	p 	eq_ref 	PRIMARY,brand_id 	PRIMARY 	4 	example_co_uk_engine.ptc.prod_id 	1 	Using where
    1 	SIMPLE 	b 	eq_ref 		PRIMARY 	PRIMARY 	2 	example_co_uk_engine.p.brand_id 	1
    Thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by sxtrail View Post
    Here is the result of an EXPLAIN. Hopefully it makes sense
    it does, except i don't know whether it's the EXPLAIN for your query or mine

    or do they have exactly the same EXPLAIN?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, sorry. That is the EXPLAIN for your query. Thanks


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
  •