I dont know how to select distinct in here

[npsari]

Hi 

 

q = "SELECT DISTINCT t.topic_id, t.topic_name, t.topic_category_id, t.topic_date, t.topic_time, u.Image1, m.category_name, u.ID, r.reply_date, r.reply_time
FROM topic t
LEFT JOIN profile u
ON u.ID = t.topic_who
LEFT JOIN category m
ON t.topic_category_id = m.category_id 
LEFT JOIN reply r
ON t.topic_id = r.reply_topic_id
ORDER BY t.topic_date DESC, t.topic_time DESC LIMIT 9;";

 

The above code does not display distinct topic_id

it gets repeated many times

Can you tell me how to fix it please

[Barand]

DISTINCT refers to the whole row - you get unique combinations of row contents.

 

If you are joining table and have multiple matching records then you are going to get the id repeated for each matching row.

 

One way is to use GROUP BY with GROUP_CONCAT()

[npsari]

I added the GROUP BY, it worked and thinsga re not repeated, but now, the topics are not ordered by the latest reply (r.reply_date, r.reply_time)

 

$q = "SELECT DISTINCT t.topic_id, t.topic_name, t.topic_category_id, t.topic_date, t.topic_time, u.Image1, m.category_name, u.ID, r.reply_date, r.reply_time

FROM topic t

LEFT JOIN profile u

ON u.ID = t.topic_who

LEFT JOIN category m

ON t.topic_category_id = m.category_id

LEFT JOIN reply r

ON t.topic_id = r.reply_topic_id

GROUP BY t.topic_id

ORDER BY r.reply_date DESC, r.reply_time DESC LIMIT 9;";

 

Any other hint plz

[Barand]

You haven't tried all the first hint yet

[npsari]

can you lead me the way please

[npsari]

if it is hard, thats fine

 

i think i know what the problem

 

In the table reply  reply_topic_id is repeated many times for each topic, that is why the query is selecting any reply_date

[Barand]

Not tested - I don't have your data

 

<?php
$q = "SELECT t.topic_id, t.topic_name, t.topic_category_id, t.topic_date, t.topic_time, 
    u.Image1, m.category_name, u.ID, 
    GROUP_CONCAT(r.reply_date, ' ', r.reply_time ORDER BY r.reply_date DESC,r.reply_time DESC SEPARATOR '<br/>') as replies
FROM topic t
LEFT JOIN profile u
ON u.ID = t.topic_who
LEFT JOIN category m
ON t.topic_category_id = m.category_id
LEFT JOIN reply r
ON t.topic_id = r.reply_topic_id
GROUP BY t.topic_id";

[npsari]

wow, cool

ill give it a try

thanks for the help barand