...

View Full Version : Searching data in a many to many relationship



Jak-S
07-19-2006, 06:00 PM
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.

Beagle
07-19-2006, 06:51 PM
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.

guelphdad
07-19-2006, 07:18 PM
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.

guelphdad
07-19-2006, 07:36 PM
As an example, your tables may be different but this may help.



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



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


having count(*) >=5

Jak-S
07-20-2006, 10:42 AM
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:


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:


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:


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 :D .

guelphdad
07-20-2006, 03:40 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum