join, and distinct in one query? 1

[youradds]

Hi,

I'm trying to do this query:

select a.user_username, a.user_id, a.user_real_name2 from gforum_User as a
        inner join gforum_Message as b
        on b.to_user_id_fk = a.user_id
        where b.to_user_id_fk = 309804 order by user_username ASC

This works, but what I really want to do is something like:

select DISTNCT(a.user_username), a.user_id, a.user_real_name2 from gforum_User as a
        inner join gforum_Message as b
        on b.to_user_id_fk = a.user_id
        where b.to_user_id_fk = 309804 order by user_username ASC

..so I get something like:

user_id user_username
1234 someone
2344 another person

...instead of what I get now:


user_id user_username
1234 someone
2344 another person
1234 someone
1234 someone
2344 another person

(i.e repeating the same person)

Is this even possible?

TIA

Andy

 

[r937]

SELECT DISTINCT
       u.user_username
     , u.user_id
     , u.user_real_name2 
  FROM gforum_Message AS m        
INNER 
  JOIN gforum_User as u
    ON u.user_id = m.to_user_id_fk       
 WHERE m.to_user_id_fk = 309804 
ORDER 
    BY u.user_username ASC

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[IanWaterman]

Looks like your field

a.user_real_name2

has data in it breaking down the distinct
try

select DISTNCT a.user_username, a.user_id, a.user_real_name2 from gforum_User as a
        inner join gforum_Message as b
        on b.to_user_id_fk = a.user_id
        where b.to_user_id_fk = 309804 order by user_username ASC

or

select a.user_username, a.user_id, maximum(a.user_real_name2) from gforum_User as a
        inner join gforum_Message as b
        on b.to_user_id_fk = a.user_id
        where b.to_user_id_fk = 309804 group by user_username, a.user_id

 

[youradds]

Thanks r937, worked a charm Star coming your way

Cheers

Andy