Mysql many-to-many realtionship multiple condition

Question

I have three tables, for example:

watch table

id(int)  name(varchar)  ref(int)
1        tissot         1234567
2        addidas        7654321
3        nike           8976543

property table

id(int)  name(varchar)
1        water_resistant
2        material_body
3        material_bracelet
4        watch_type

property_material table

watch_id  property_id   value
1              1          200
1              2         steel
1              3         leather
1              4         quartz
2              1          50
2              2         plastic
2              3         leather
2              4         quartz
3              1         100
3              2         steel
3              3         rubber
3              4        mechanical

I want get all watches which have properties for example:

water_resistant >= 100
material_body = steel or plastic
material_bracelet = leather or rubber
watch_type = quartz.

Help me please create query. Properties can be about 100. In ideal i want get result:

watch_id name    ref   water_res mat_body mat_bracelet watch_type
1        tissot  1234   200       steel    leather      quartz

I'm try:

select 
 w.*, pm.value,pm.property_id 
from 
 watch w 
join 
  property_material pm 
 on w.id = pm.watch_id 
where 
  pm.pid in (1,2,3) 
and 
 pm.value in ('100','Steel','Leather');

But this query return all records, when value is Steel or Leather, bur I need where material_body is Steel and material_bracelet is Leather, for example.

UPDATE: What you are think about this query?

select 
  name, ref 
from
  watch w
join
(SELECT w.id
  FROM watch w
  JOIN property_material pm on w.id = pm.watch_id
  WHERE (pm.property_id = 1 AND pm.value > 100)
   OR (pm.property_id = 2 AND pm.value IN ('steel','plastic'))
   OR (pm.property_id = 3 AND pm.value IN ('leather','rubber'))
   OR (pm.property_id = 4 AND pm.value = 'quartz')
  Group by w.id
  Having count(*) = 4) as t1
 on t1.id = w.id;

LAST UPDATE:

select 
  w.name, 
  max(if(pm.property_id='1',pm.value,'')) water_resistant,
  max(if(pm.property_id='2',pm.value,'')) material_body,
  max(if(pm.property_id='3',pm.value,'')) material_bracelet,
  max(if(pm.property_id='4',pm.value,'')) watch_type
from
  watch w
join
(
    SELECT w.id
  FROM watch w
       JOIN property_material pm on w.id = pm.watch_id
  WHERE (pm.property_id = 1 AND pm.value > 100)
      OR (pm.property_id = 2 AND pm.value IN ('steel','plastic'))
      OR (pm.property_id = 3 AND pm.value IN ('leather','rubber'))
      OR (pm.property_id = 4 AND pm.value = 'quartz')
  Group by w.id
  Having count(*) = 4
  ) as t1 on t1.id = w.id
 join property_material pm on w.id = pm.watch_id
GROUP BY w.id

Can you post a sample set of what you need as result? Do you have some code, have you tried something? — Yaroslav, Oct 25 '12 at 07:24
You are welcome but no need to thank. All the conditions should accomplished in one sentence or they are all independent? — Yaroslav, Oct 25 '12 at 07:30
Thank you for reply! I like get tuple: id, watch, ref, water_resistant, mateterial_body, material_braclet, watch_type. I'm try this: `select w.*, pm.value,pm.pid from watch w join property_material pm on w.id = pm.watch_id where pm.pid in (1,2,3) and pm.value in ('100','Steel','Lather');` - but this query return all records, when value is Steel or Leather. — vedmed, Oct 25 '12 at 07:43
Edit your question and add you code there, do not forget to format it for better understanding. — Yaroslav, Oct 25 '12 at 07:46
Your last query has a typo, should be `leather`, not `lather`. But I don't know if that is the result you need. [**Check here for test**](http://sqlfiddle.com/#!2/9587b/21) — Yaroslav, Oct 25 '12 at 07:53
Yaroslav, can i do grouping by watch_id, and get all result in one set? — vedmed, Oct 25 '12 at 08:11

Yaroslav · Accepted Answer · 2012-10-25T07:52:08.660

Here you have several queries that fill your needs. But as you see, they are all independent. That is why I ask in the comments what exactly you need.

For more tests use this SQL Fiddle sample code using your provided sample data.

EDIT Added new query to have all conditions and all results on one set. Check SQL Fiddle sample code here.

SELECT w.name,ref,property_id,value 
  FROM watch w
  JOIN property p on w.id = p.id
  JOIN property_material pm on p.id = pm.property_id 
 WHERE (p.id = 1 AND pm.value > 100)
    OR (p.id = 2 AND pm.value IN ('steel','plastic'))
    OR (p.id = 3 AND pm.value IN ('leather','rubber'))
    OR (p.id = 4 AND pm.value = 'quartz');

Independent queries:

SELECT w.name Watch_name, p.name Property_name, pm.value Property_value
  FROM watch w
  JOIN property_material pm ON w.id = pm.watch_id
  JOIN property p ON p.id = pm.property_id
 WHERE p.id = 1
   AND pm.value > 100;

WATCH_NAME  PROPERTY_NAME     PROPERTY_VALUE
tissot      water_resistant   200

Another one:

SELECT w.name Watch_name, p.name Property_name, pm.value Property_value
  FROM watch w
  JOIN property_material pm ON w.id = pm.watch_id
  JOIN property p ON p.id = pm.property_id
 WHERE p.id = 2
   AND pm.value IN ('steel','plastic');

WATCH_NAME   PROPERTY_NAME   PROPERTY_VALUE
tissot      material_body      steel
addidas     material_body      plastic
nike        material_body      steel

Another one:

SELECT w.name Watch_name, p.name Property_name, pm.value Property_value
  FROM watch w
  JOIN property_material pm ON w.id = pm.watch_id
  JOIN property p ON p.id = pm.property_id
 WHERE p.id = 3
   AND pm.value IN ('leather','rubber');

WATCH_NAME   PROPERTY_NAME      PROPERTY_VALUE
tissot      material_bracelet      leather
addidas     material_bracelet      leather
nike        material_bracelet      rubber

Another one:

SELECT w.name Watch_name, p.name Property_name, pm.value Property_value
  FROM watch w
  JOIN property_material pm ON w.id = pm.watch_id
  JOIN property p ON p.id = pm.property_id
 WHERE p.id = 4
   AND pm.value = 'quartz';

WATCH_NAME   PROPERTY_NAME   PROPERTY_VALUE
tissot         watch_type       quartz
addidas        watch_type       quartz

Thanks, but in you result is Nike. Nike is mechanical watch. It is possible to make strict query, only: water_resistant >= 100, watch_type = quartz, material_body = steel or plastic and etc? — vedmed, Oct 25 '12 at 08:22
If i change `OR (p.id = 4 AND pm.value = 'quartz123')`, result don't change. — vedmed, Oct 25 '12 at 08:34
That is because the watches on the result set already accomplish some of the previous conditions and even removing the last condition everything remains the same. That query was just to gather in one result set all the values, without any other restriction — Yaroslav, Oct 25 '12 at 08:57
What you think about this query: `select name, ref from watch w join (SELECT w.id FROM watch w JOIN property_material pm on w.id = pm.watch_id WHERE (pm.property_id = 1 AND pm.value > 100) OR (pm.property_id = 2 AND pm.value IN ('steel','plastic')) OR (pm.property_id = 3 AND pm.value IN ('leather','rubber')) OR (pm.property_id = 4 AND pm.value = 'quartz') Group by w.id Having count(*) = 4) as t1 on t1.id = w.id` ? — vedmed, Oct 25 '12 at 09:53
Well, seems to be working, at least returns the result set you want. But test carefully, subqueries together with `group by` and `having` can be tricky. Here is the [**SQL Fiddle code**](http://sqlfiddle.com/#!2/9587b/89) with your proposal, for you to test. I'm the middle of other things now, can't test, sorry — Yaroslav, Oct 25 '12 at 09:59

score 1 · Answer 2 · answered Oct 25 '12 at 07:47

1

The only possibility i know is to make a join to the properties table for each property, like so (i modified your given example):

select w.*, pm1.value,pm1.pid ,pm2.value,pm2.pid
    from watch w 
        join property_material pm1 on w.id = pm1.watch_id AND pm1.pid = 1 and pm1.value = '100'
        join property_material pm2 on w.id = pm2.watch_id AND pm2.pid = 2 and pm2.value IN ('Steel','Lather');

answered Oct 25 '12 at 07:47

Argeman

1,345
8
22

Thanks for reply! If my condition consist by 20 properties, i can do 20 joins? – vedmed Oct 25 '12 at 08:24
Yes, why not? But there is actually a limit of 61 tables for joins in MySQL. Your solution in the update feels better for me, there should be much more checks possible... – Argeman Oct 25 '12 at 10:13
@vedmed I searched for further information for your problem; one nice answer can be found here: http://stackoverflow.com/a/1685034/1338150. I hope that helps you... – Argeman Oct 25 '12 at 10:23

Shabarinath Volam · Answer 3 · 2012-10-25T07:46:52.030

0

select watch_table.id,watch_table.name,ref,property_id,value from watch_table join        property_table on watch_table.id=property_table.id
  join property_material on property_table.id=property_material.property_id 
 where WHERE property.id = 3 AND property_material.value IN ('leather','rubber');

edited Oct 25 '12 at 07:46

answered Oct 25 '12 at 07:38

Shabarinath Volam

789
5
19
48

Mysql many-to-many realtionship multiple condition

3 Answers3