[SOLVED] SQL Query With SELECT DISTINCT and LEFT JOIN

[tcollie]

I'm having problems with a SELECT DISTINCT statement with an INNER JOIN.

 

Here is the table structure for my tables:

 

'locked_accounts'

locked_account_id

uid

 

'la_comments'

comment_id

locked_case_id

uid

status

 

'user'

uid

username

 

And here is my SQL:

SELECT DISTINCT l.locked_case_id as locked_case_id, l.uid, u.uid as uid, u.username as username FROM la_comments l LEFT JOIN users u ON l.uid = u.uid where status = 1

 

What I'm trying to accomplish is getting a display of updated accounts (where status=1) and I want to display their username.  In the la_comments table, there might be 50 distinct 'uid's, but I only want to display the distinct uid/username's where the status = 1.

 

Can someone point me in the right direction on this. I've messed with this for a couple of hours and I'm at wits end.  Not very bright I am when JOIN statements it comes to. 

[emehrkay]

have you tried it withou tthe distinct?

[tcollie]

yep.  no luck by removing DISTINCT.

 

I use the following SQL to get the actual count of DISTINCT updated records

 

select distinct locked_case_id from la_comments where status = 1

 

and it works well.  I just can't figure out how to join it on my users table to get their username.

[emehrkay]

i think when you do distinct, you have to do group by

[tcollie]

Actually, believe it or not, you don't have to group.  After more fidgeting with the code, and kicking my dog (i love my computer more than the dog) this finally fixed it:

 

SELECT DISTINCT l.locked_case_id as locked_case_id, l.uid, u.username as username FROM la_comments l LEFT JOIN users u on l.uid = u.uid where l.status = 1

 

BTW, I didn't really kick my dog.  Thanks for everyone's help on this.