title: MySQL SELECT query from multiple tables at the same time
body: <p>I have schema like this in my database:
  <a href="https://i.stack.imgur.com/iM3ok.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/iM3ok.jpg" alt="enter image description here"></a></p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <ul>
  <li>recipe.<code>unique_id</code>,   </li>
  <li>recipe.<code>title</code>,   </li>
  <li>recipe.<code>img_tumbnail_link</code>,  </li>
  <li>recipe.<code>add_date</code>,</li>
  <li>recipe.<code>kitchen_type</code>,   </li>
  <li>recipe.<code>meal_type</code>,    </li>
  <li>user.<code>name</code>,  </li>
  <li>user.<code>surname</code>,   </li>
  <li>COUNT(<code>like</code>.<code>recipe_unique_id_fk</code>) AS like_count</li>
  </ul>
  
  <p>I've tried to do some query with this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>From table like this:</p>
  
  <p><a href="https://i.stack.imgur.com/6e9by.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6e9by.jpg" alt="enter image description here"></a></p>
  
  <p>with this query i receive only 1 row instead of 3 I should get for user with id: <code>565096811c0b51.08471830</code>. Any help? Thank you.</p>
  
  <p>I've added recipes table and result:)</p>
  
  <p><a href="https://i.stack.imgur.com/dzF7y.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/dzF7y.jpg" alt="enter image description here"></a></p>
  
  <p>Here is my query result:</p>
  
  <p><a href="https://i.stack.imgur.com/vgnfQ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vgnfQ.jpg" alt="enter image description here"></a></p>
  
  <p>Here is all records (no duplicate): <a href="http://postimg.org/image/ejjemnozb/" rel="nofollow noreferrer">http://postimg.org/image/ejjemnozb/</a></p>