0
SELECT SUBSTRING(m.content,1,20) as content, 
       m.viewed, m.sent_date, 
       u.username 
FROM message m 
INNER JOIN user u 
ON u.userid = m.message_from
WHERE m.message_to = :userid

My problem with this query is that it's selecting multiple rows from the same username. I just want it to select one row per username.

I believe I use DISTINCT for this?

I couldn't get it working, can someone assist me!

Thanks.

kabuto178
  • 3,129
  • 3
  • 40
  • 61
sark9012
  • 5,485
  • 18
  • 61
  • 99

2 Answers2

1
SELECT SUBSTRING(m.content,1,20) as content, m.viewed, m.sent_date, u.username 
FROM message m INNER JOIN user u ON u.userid = m.message_from
WHERE m.message_to = :userid GROUP BY u.userid
enapupe
  • 15,691
  • 3
  • 29
  • 45
  • 1
    This is not a way `GROUP BY` should be used. Try this on a non-MySQL DBMS. – Kermit Mar 31 '13 at 17:40
  • Group by! I've used it a while back but couldn't remember, thought it was distinct! Thanks bud. @PolishPrince > what's the problem with this solution? – sark9012 Mar 31 '13 at 17:44
  • @Luke Read [this](http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html). – Kermit Mar 31 '13 at 18:29
-1

Yeah !You can use distinct in your username 

SELECT Distinct( u.username ), SUBSTRING(m.content,1,20) as content, 
       m.viewed, m.sent_date           
FROM message m 
INNER JOIN user u 
ON u.userid = m.message_from
WHERE m.message_to = :userid
Ramesh Rajendran
  • 37,412
  • 45
  • 153
  • 234
  • Ahhh you need brackets around the field. I didn't try that. In your opinion, what's the better approach? Group by or distinct? – sark9012 Mar 31 '13 at 17:55
  • 1
    DISTINCT is not a function that applies only to some columns. It's a query modifier that applies to all columns in the select-list. Follow http://stackoverflow.com/questions/7250566/mysql-select-distinct/7250654#7250654 – enapupe Mar 31 '13 at 17:58