Multiple rows returned in OR query

[raymon]

Hi,

sorry to post such a basic question in the 'expert' forum, but I've been all day trying to solve this (apparently simple) problem with no success.

I've made up an example so my problem is easy to understand...

I have the following tables:

mysql> select * from food;
+----+--------+
| id | name |
+----+--------+
| 1 | orange |
| 2 | meat |
| 3 | apple |
| 4 | water |
+----+--------+

mysql> select * from calories;
+--------+------+
| foodId | Kcal |
+--------+------+
| 1 | 10 |
| 2 | 75 |
| 3 | 5 |
| 4 | 0 |
+--------+------+

mysql> select * from similar;
+----------+----------+------------+
| foodId_A | foodId_B | similarity |
+----------+----------+------------+
| 1 | 2 | 10 |
| 1 | 3 | 80 |
| 3 | 2 | 15 |
| 1 | 4 | 5 |
| 4 | 2 | 5 |
| 3 | 4 | 5 |
+----------+----------+------------+

(in this table, the pairs (foodId_A, foodId_B) do not follow any rules in terms of which food is A or B. Therefore, we will have to test whether our food of interest is foodId_A or foodId_B)


Now, I want to get all foods that are either similar to an orange (similarity>50) or have a lot of calories (Kcal>70). That is, I want to get any food that respects at least one of the two conditions: similar to an orange or high calory count.

The answer in this simple example should be 'apple' (it is similar to an orange) and 'meat' (it has lots of calories).

When I do the query...

select * from food, similar, calories where (calories.foodId=food.id and calories.Kcal>50) or ((similar.foodId_A=food.id and similar.foodId_B=1 and similar.similarity>50) or (similar.foodId_B=food.id and similar.foodId_A=1 and similar.similarity>50) );

('orange' has id 1)

... I get:

+----+-------+----------+----------+------------+--------+------+
| id | name | foodId_A | foodId_B | similarity | foodId | Kcal |
+----+-------+----------+----------+------------+--------+------+
| 2 | meat | 1 | 2 | 10 | 2 | 75 |
| 3 | apple | 1 | 3 | 80 | 1 | 10 |
| 2 | meat | 1 | 3 | 80 | 2 | 75 |
| 3 | apple | 1 | 3 | 80 | 2 | 75 |
| 3 | apple | 1 | 3 | 80 | 3 | 5 |
| 3 | apple | 1 | 3 | 80 | 4 | 0 |
| 2 | meat | 3 | 2 | 15 | 2 | 75 |
| 2 | meat | 1 | 4 | 5 | 2 | 75 |
| 2 | meat | 4 | 2 | 5 | 2 | 75 |
| 2 | meat | 3 | 4 | 5 | 2 | 75 |
+----+-------+----------+----------+------------+--------+------+

which is the 'apple', 'meat' answer I was expecting (if applying distinct to the select). But this is clearly not efficient, since in a real database there are so many records that I am getting thousands of duplicate rows and it takes forever to get an answer.

I understand this is happening because mysql is doing a cartesian product between table similarities (when applying the condition on calories) and table calories (when applying the condition on similarity). But I don't know how to solve this...

What else do I have to include in my query so that those duplicate rows do not come back as an answer?

I asked the same question in another forum and somebody suggested using Views, but he was not sure it was the best way to do it and I'd like to avoid using Views.

Thank you very much for your help,

raymon

[raymon]

Well... After asking everywhere, I finally found the answer by myself. I think this is the most optimal way to do the query I was looking for. If someone thinks there is a better answer, I'll be very happy to hear about it

SELECT *

FROM calories JOIN food LEFT JOIN similar ON

((similar.foodId_A=food.id and similar.foodId_B=1 and food.id=calories.foodId)

or

(similar.foodId_B=food.id and similar.foodId_A=1 and food.id=calories.foodId))


WHERE (Kcal>50 or similarity>50) and similarity IS NOT NULL;