CODEWITHSUNDEEP
phpsql
user8014124
user8014124

Reputation:

SQL SELECT DISTINCT doesn't return distinct columns

I'm making a forum where i want the latest topics to be shown side-by-side with the category in which it belongs. However, when someone makes a topic in the same category twice it shows the category twice and the different topics on the right side.

This is somewhat right, but I want it to be only one category showing. I tried SQL SELECT DISTINCT for this, but it doesn't seem to work, can anyone tell me if there is something wrong in the syntax or if the problem lies elsewhere?

SELECT DISTINCT topics.topic_id, topics.topic_subject, topics.topic_by, categories.cat_id, categories.cat_name, categories.cat_description 
FROM topics JOIN categories ON topics.topic_cat = categories.cat_id 
ORDER BY topics.topic_date DESC LIMIT 3

Also, I want to note that the reason for LIMIT 3 is because this is from the homepage, where only 3 categories are supposed to show. The problem persists on both the homepage and the category page.

Relevant tables (Pictures):

Categories

Topics

Upvotes: 0

Views: 84

Answers (2)

JYoThI
JYoThI

Reputation: 12085

Your trying DISTINCT for topic_id column you need to change it to topic_subject column

topic_id column looks like auto_increment primary key so it always DISTINCT .

change like this 

SELECT DISTINCT topics.topic_subject, topics.topic_id,
topics.topic_by, categories.cat_id, categories.cat_name, 
categories.cat_description 
FROM topics JOIN categories ON topics.topic_cat = categories.cat_id 
GROUP BY  categories.cat_name
ORDER BY topics.topic_date DESC LIMIT 3

Upvotes: 0

Mike Adamenko
Mike Adamenko

Reputation: 3002

You don't need DISTINCT in this case.

To show only one category you need to use GROUP BY clause to group you topics.

Try following sql 

SELECT  topics.topic_subject, topics.topic_by, categories.cat_name, categories.cat_description, count(categories.cat_name) 
FROM topics JOIN categories ON topics.topic_cat = categories.cat_id GROUP BY  categories.cat_name
ORDER BY topics.topic_date DESC LIMIT 3

Upvotes: 1

Related Questions