Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Mar 2005
    Location
    Brighton, UK
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Searching data in a many to many relationship

    Hi
    I need to work out how to search some data in a MySQL database but Iím completely stuck and donít have a clue how to do it.

    There are four tables. Variables contains a list of different variables that can be associated to products. Options is a child of variables and contains all the different options available for the variable (for example a variable called Colour may have Blue, Red and Green options). Products contains all the products available.

    The last table is called product_options and links the products table to the options table in a many-to-many relationship. So, you may have two variables called Colour and Size, and then a product that is linked to Blue and Large.

    My question is, how do I search this data to find say, all the products that are blue, or all the products that are blue and large etc.

    All Iíve got so far is a query that links the four tables together using inner joins, however this is totally wrong as a product thatís linked to three options will appear three times, and grouping them doesnít help because then you loose two options that you may wish to search on.

    Thanks in advance, Iím REALLY stuck with this.

  • #2
    Senior Coder
    Join Date
    Jul 2005
    Location
    New York, NY
    Posts
    1,084
    Thanks
    4
    Thanked 19 Times in 19 Posts
    I think you might be confused about what you should be expecting back from a query.

    If you want just the options, then you'll get
    blue
    large
    heavy

    If you want the just the products you'll get
    stroller
    swingset

    If you want the products AND their options, you'll get
    stroller blue
    stroller large
    stroller heavy
    swingset blue
    swingset large
    swingset heavy

    if you want to find all products that are blue AND heavy, then just use group by, you already know they are blue and heavy, why do you need that data returned? On the other hand, if you want to know what options a product has, then just get the options, you already know the product. Lastly, if you just want to see everything, then either do two queries and combine the data in your application, or bring over the redundant product data.

    I think you're on the right track, you just need to better define your goal, and your answer will become clear.

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    show us the query you have and we can tweak it from there. it may also help to give us some sample data from your tables.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    As an example, your tables may be different but this may help.

    Code:
    table products:
    product_id, product
    1 car
    2 house
    3 dog
    
    table variables:
    variable_id, variable
    (1, 'red'),
    (2, 'wheels'),
    (3, 'fast'),
    (4, 'convertable'),
    (5, 'windows'),
    (6, 'pet'),
    (7, 'barks'),
    (8, 'roof'),
    (9, 'blue'),
    (10, 'makes me happy');
    
    products_variables
    pid, vid
    (1,1),
    (1,2),
    (1,3),
    (1,4),
    (1,5),
    (1,8),
    (1,10),
    (2,5),
    (2,8),
    (2,10),
    (3,6),
    (3,7),
    (3,10);
    I want to show all products that have a roof AND make me happy

    Code:
    select
    products.product
    from
    products
    left join products_variables
    on
    products.product_id = products_variables.pid
    left join variables
    on 
    products_variables.vid = variables.variable_id
    where variables.variable IN ("roof", "makes me happy")
    group by
    product
    having count(*) = 2
    now to increase the number of variables you are looking for change the IN list to add the extra variables and change the count to however many items are in the IN list.

    AND you can even get ones with part answers. perhaps your IN list has 10 attributes and you only want items with 5 or more then change the IN list and then the having clause becomes

    Code:
    having count(*) >=5

  • #5
    Regular Coder
    Join Date
    Mar 2005
    Location
    Brighton, UK
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, thanks for the help. guelphdad:

    Your query is exactly the sort of thing i was looking for, infact i made a start on a similar one before your post:

    Code:
    SELECT products.*,
    	COUNT(*) AS matches
    FROM products
    	INNER JOIN product_options ON product_options.product_id = products.id
    WHERE product_options.option_id IN (10, 19)
    GROUP BY products.id
    But i couldnt figure out how to limit it to only the ones that matched all the options. I was trying to do this, but it produced an error:

    Code:
    SELECT products.*,
    	COUNT(*) AS matches
    FROM products
    	INNER JOIN product_options ON product_options.product_id = products.id
    WHERE product_options.option_id IN (10, 19)
    	AND matches = 2
    GROUP BY products.id
    Im guessing you cant use aggerated data like that in the WHERE clause? Anyway, it looks like the HAVING bit is what i needed, like this:

    Code:
    SELECT products.*,
    	COUNT(*) AS matches
    FROM products
    	INNER JOIN product_options ON product_options.product_id = products.id
    WHERE product_options.option_id IN (10, 19)
    GROUP BY products.id
    HAVING matches = 2
    Thanks very much, it all works perfectly now .

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    right you are about the WHERE clause. Think of your query in the order it is written, the WHERE clause comes before the GROUP BY and the HAVING clause afterwards. So when you want to count something in a group it must be done after the group is created, that is why the HAVING clause is used.


  •  

    Posting Permissions

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