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.

Looks like those results are assigned to 2 different subcategories. — Dez
– Dez, Commented Dec 22, 2018 at 19:37
add a proper data sample the actual result and the expected result — ScaisEdge
– ScaisEdge, Commented Dec 22, 2018 at 19:39
How many subcategories are there for the category? Two? The subcategory doesn't look like it's assigned to a post at least your joins imply that. Or maybe it is in fact the subcategory, that is referenced in a post? In that case join the subcategories to the posts and the categories to the subcategories: INNER JOIN subcategories ON(posts.category_id = subcategories.id) INNER JOIN categories ON (categories.id = subcategories.category_id) — sticky bit
– sticky bit, Commented Dec 22, 2018 at 19:40

GMB · Accepted Answer · 2018-12-23 00:09:39Z

2

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

edited Dec 23, 2018 at 0:09

answered Dec 22, 2018 at 19:40

GMB

224k25 gold badges103 silver badges151 bronze badges

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

12 Comments

Akshay Shrivastav Over a year ago

Yes exactly happened as you mentioned, the post was not having subcategory so it was not being mentioned. After using the LEFT JOIN it was visible, now the data is being displayed but the id 30 is being repeated twice how to avoid that.

GMB Over a year ago

Hello @AkshayShrivastav, that's because you have two subcategories matching that post. What do you want to do in this use case ?

Akshay Shrivastav Over a year ago

I want to eliminate the rows that are having same id posts.id is primary

GMB Over a year ago

well, you will need to remove the INNER JOIN subcategory (and field subcategories.name from the SELECT query)

Akshay Shrivastav Over a year ago

but i want that query same, if i remove the subcategory, query will be of no use, any alternate to it.

|

Collectives™ on Stack Overflow

MySQL Query returning same row twice

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related