MySQL SELECT query from multiple tables at the same time

Question

I have schema like this in my database:

I want to display all recipes (with specific fields) which specific user (given with unique_id) has added to his favourites which are desscribed in table favourite (2 fields - user id and liked recipe). I have no idea how to to that.

f.e. If user likes 5 recipes, that information is included in the favourite table (his id and recipe he liked id). I want to display fields:

recipe.unique_id,
recipe.title,
recipe.img_tumbnail_link,
recipe.add_date,
recipe.kitchen_type,
recipe.meal_type,
user.name,
user.surname,
COUNT(like.recipe_unique_id_fk) AS like_count

I've tried to do some query with this:

SELECT recipe.`unique_id`, recipe.`title`, recipe.`img_tumbnail_link`, recipe.`add_date`, recipe.`kitchen_type`, recipe.`meal_type`, user.`name`, user.`surname`, COUNT(`like`.`recipe_unique_id_fk`) AS like_count 
FROM `recipe` 
JOIN `favourite` ON (recipe.`unique_id` = `favourite`.`recipe_unique_id_fk`) 
JOIN `like` ON (recipe.`unique_id` = `like`.`recipe_unique_id_fk`) 
JOIN `user` ON (recipe.`user_unique_id_fk` = user.`unique_id`)
WHERE favourite.`user_unique_id_fk` = '565096811c0b51.08471830' 
ORDER BY recipe.`add_date` DESC

From table like this:

with this query i receive only 1 row instead of 3 I should get for user with id: 565096811c0b51.08471830. Any help? Thank you.

I've added recipes table and result:)

Here is my query result:

Here is all records (no duplicate): http://postimg.org/image/ejjemnozb/

Without seeing the data I'm finding it hard to walk through it. Can you put a sample up on sqlfiddle? Also, have you tried omitting the like components? Let's start with the easy parts first and gradually add more layers :) — Rich Seviora
– Rich Seviora, Commented Nov 28, 2015 at 0:36
@RichardSeviora I've added recipes table and my query result as u wanted :) — anton86993
– anton86993, Commented Nov 28, 2015 at 0:42

Furqan Aziz · Accepted Answer · 2015-11-28 00:43:21Z

2

You are using join with like table as well which is having only one row thats why only one row is returned. You can calculate likes with sub-query. I have mentioned correct query below:

SELECT recipe.`unique_id`, recipe.`title`, recipe.`img_tumbnail_link`, recipe.`add_date`, recipe.`kitchen_type`, recipe.`meal_type`, user.`name`, user.`surname`, 
(SELECT count(*) from `like` WHERE recipe.`unique_id` = `like`.`recipe_unique_id_fk`) AS like_count
FROM `recipe` 
JOIN `favourite` ON (recipe.`unique_id` = `favourite`.`recipe_unique_id_fk`) 
JOIN `user` ON (recipe.`user_unique_id_fk` = user.`unique_id`)
WHERE favourite.`user_unique_id_fk` = '565096811c0b51.08471830' 
ORDER BY recipe.`add_date` DESC

answered Nov 28, 2015 at 0:43

Furqan Aziz

1,1149 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

anton86993 Over a year ago

Look like it is fine. I just added records to like table instead of favourite table :D Thx now it works :) Can you also check this one: pastebin.com/LsRtwSeX (it selects top 5 recipes). It works fine but can it be made more simple?

Furqan Aziz Over a year ago

This query is also having same error. Just use the query I mentioned above with one addition in end ` LIMIT 5`

Collectives™ on Stack Overflow

MySQL SELECT query from multiple tables at the same time

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related