SQL select query almost works, except for the ORDER BY

Question

I've got a query that very nearly works, except for the ORDER BY part.

In Plain English, I'm selecting the author_ids for authors who've posted entries to a particular channel, and limiting it by a few member groups.

(why is this a query? Will use this as basis to list entries in channel by member, limiting the number of entries/author to 4)

So far, so good. This Query works:

 {exp:query sql="SELECT DISTINCT author_id 
    FROM exp_channel_titles 
    JOIN exp_members ON exp_channel_titles.author_id = exp_members.member_id 
    WHERE exp_channel_titles.channel_id='5' 
    AND exp_channel_titles.status = 'open'
    AND (exp_members.group_id = '5' OR exp_members.group_id = '6' OR exp_members.group_id = '7' OR exp_members.group_id = '8')"}

It works, but the listing needs to be ordered differently. I want to order them alphabetically by member last name.

Am using Profile:Edit and have custom channel fields for member_first_name and member_last_name (field_id_50)

I need to refer to exp_channel_titles twice. Here's a graphic representation of what info I'm referring to in the database:

 ct  exp_channel_titles             exp_members
     ==================             ===========
     author_id                      member_id
     status = open                  group_id = 5|6|7|8
     channel_id = 5

 ct2 exp_channel_titles             exp_channel_data
     ==================             ================
     entry_id                       entry_id
     channel_id = 12                field_id_50
     author_id

Here's my second query. It doesn't throw errors (um, yay?), but it does not change the order of the results, either.

SELECT DISTINCT ct.author_id
FROM exp_channel_titles AS ct 
JOIN exp_channel_titles AS ct2 ON ct.author_id = ct2.author_id
JOIN exp_members ON ct.author_id = exp_members.member_id
JOIN exp_channel_data ON exp_channel_data.entry_id = ct2.author_id
WHERE ct.channel_id = 5
AND ct.status = 'open'
AND ct2.channel_id = 12
AND (exp_members.group_id = 5 OR exp_members.group_id = 6 OR exp_members.group_id = 7 OR exp_members.group_id = 8)
ORDER BY exp_channel_data.field_id_50 ASC

changing the first line does not produces an empty column of results for last name SELECT DISTINCT ct.author_id, exp_channel_data.field_id_50 FROM etc.

How do I get my ORDER BY to work?

Answer 1

For a start, this line is incorrect...Entry_id <> author_id

JOIN exp_channel_data ON exp_channel_data.entry_id = ct2.author_id

Take your query and paste it into the inbuilt query form. Remove or add query lines one by one and test your query output until you get what you want.