How to write such SQL query statement?

Question

I have four tables: foods, recipes, users, and activities.

The activities table has schema:

mysql> describe activities;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id   | int(11)      | YES  | MUL | NULL    |                |
| recipe_id | int(11)      | YES  | MUL | NULL    |                |
| name      | varchar(255) | YES  |     | NULL    |                |
| amount    | int(11)      | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

The recipes table has schema:

mysql> describe recipes;
    +--------------+--------------+------+-----+---------+----------------+
    | Field        | Type         | Null | Key | Default | Extra          |
    +--------------+--------------+------+-----+---------+----------------+
    | id           | int(11)      | NO   | PRI | NULL    | auto_increment |
    | image_url    | varchar(255) | YES  |     | NULL    |                |
    | user_id      | int(11)      | YES  | MUL | NULL    |                |
    | food_id      | int(11)      | YES  | MUL | NULL    |                |
    +--------------+--------------+------+-----+---------+----------------+

The foods-recipes has a one-to-many relationship, and recipes-activities has a many-to-many relationships.

Now I want to select all user activities on foods. I consider a user acting on a food if he acts on a recipe of that food. If a user acts on multiple recipes of the same food, only one should be counted.

Could anyone tell me how to write the query?

Please read the rules. You are not allowed to ask for answers to questions you have not attempted yourself! Rule: Do not ask questions you haven't tried to find an answer for (show your work!) — Stephen Rodriguez, Aug 11 '14 at 04:20
@Mtn_Wolf Is it so? [`See your first question in SO`](http://stackoverflow.com/q/19749599/2118383) — , Aug 11 '14 at 04:22
He is not asking for a solution to a problem. They asked for a method to resolve his situation. — Stephen Rodriguez, Aug 11 '14 at 04:24
use a [`JOIN`](http://www.sitepoint.com/understanding-sql-joins-mysql-database/) — serakfalcon, Aug 11 '14 at 04:35
@serakfalcon, I don't know how to detect those recipes linking to the same food. Those activities should be counted only one time. — HanXu, Aug 11 '14 at 04:44

score 0 · Accepted Answer · answered Aug 11 '14 at 04:53

0

SELECT DISTINCT activities.user_id,recipes.food_id 
FROM activities 
INNER JOIN recipes ON recipes.id = activities.recipe_id

should pull the ID of every food from a recipe that a user uses in activities. DISTINCT ensures that there are no duplicates.

answered Aug 11 '14 at 04:53

serakfalcon

3,501
1
22
33

This will select distinct user_ids. But if user 1 acted on food 1 and 2, it should be counted twice. – HanXu Aug 11 '14 at 06:17
no, it selects distinct user/food pairs. Did you even try to run the query? http://www.techonthenet.com/mysql/distinct.php – serakfalcon Aug 11 '14 at 06:23
Sorry it is. I tried, but the first time made a mistake. Thanks! – HanXu Aug 11 '14 at 06:26

How to write such SQL query statement?

1 Answers1