MySQL Query returning same row twice

Question

I am trying to display data using 3 tables

posts
  - id
  - category_id
  - user_authors_id
  - title
  - status

user_authors
  - id
  - author_name

categories
  - id
  - name

subcategories
  - id
  - name

What I am trying to do is. I am creating a view panel that will display the posts. So, i am using 3 tables. From user_authors I'll get the author name, from categories table I'll get the category name, now the category tables have subcategory id, so I also want to get the subcategory name.

I am having two rows in the posts table with id 29 and 30 but when i run the below query it shows 2 entries with the same data.

SELECT 
   posts.id, 
   categories.name AS cat_name, 
   subcategories.name AS subcat_name, 
   posts.title, 
   user_authors.author_name, 
   posts.created, 
   posts.status
FROM posts 
INNER JOIN user_authors ON (user_authors.id = posts.user_author_id) 
INNER JOIN categories ON(posts.category_id = categories.id) 
INNER JOIN subcategories ON (categories.id = subcategories.category_id)

But, if I remove this statement INNER JOIN subcategories ON (categories.id = subcategories.category_id) and run the query, it runs perfect, all the rows are shows properly.

What's happening, I am not trying to get it. Where is the query wrong, also it's showing no error.

Answer 1

INNER JOIN subcategories ON (categories.id = subcategories.category_id)

As it is, for your query to return what you expect, there must be one and only one record in subcategories matches the given post :

• if more than one subcategory matches a given post, the post line will be duplicated in the results
• if no subcategory matches a given post, the post will not appear in the results

Depending on your use case, you want :

• not to JOIN subcategory, to avoid duplicating posts
• LEFT JOIN subcategory instead of INNER JOIN subcategory to avoid posts without subcategory to be filtered out

If you do have multiple subcategories for a given post and you still want to display a single row in the results, you can use the GROUP_CONCAT aggregate funtion to concatenate the subcategories into one field :

SELECT 
   posts.id, 
   categories.name AS cat_name, 
   GROUP_CONCAT( subcategories.name, ', ') AS subcat_names, 
   posts.title, 
   user_authors.author_name, 
   posts.created, 
   posts.status
FROM posts 
INNER JOIN user_authors ON (user_authors.id = posts.user_author_id) 
INNER JOIN categories ON(posts.category_id = categories.id) 
LEFT  JOIN subcategories ON (categories.id = subcategories.category_id)
GROUP BY 
   posts.id, 
   categories.name, 
   posts.title, 
   user_authors.author_name, 
   posts.created, 
   posts.status