ORDER RESULTS

[zzz]

I have a query that gets results from 3 different tables by using LEFT JOIN.

t1.id
t1.event
t1.t2_ID
t1.t3_ID

t2.id
t2.t2FirstName
t2.t2LastName

t3.id
t3.t3FirstName
t3.t3LastName

// QUERY

SELECT
	t1.*,
	t2.t2FirstName,
	t2.t2LastName,
	t3.t3FirstName,
	t3.t3LastName
FROM t1
LEFT JOIN t2 ON (t1.t2_ID = t2.id)
LEFT JOIN t3 ON (t1.t3_ID = t3.id)
WHERE t1.event = X
ORDER BY ????? // NEED HELP HERE!

Is there a way for me to be able to sort the list alphabetically, say by last name?

[ixalmida]

I wonder if you're really looking for the UNION of t2 and t3, but let's assume you aren't...

SELECT 
    t1.*, 
    t2.t2FirstName, 
    t2.t2LastName, 
    t3.t3FirstName, 
    t3.t3LastName 
FROM t1 
LEFT JOIN t2 ON (t1.t2_ID = t2.id) 
LEFT JOIN t3 ON (t1.t3_ID = t3.id) 
WHERE t1.event = X 
ORDER BY t2.t2LastName, t2.t2FirstName, t3.t3LastName, t3.t3FirstName

[zzz]

I do use all three tables. t1 contains event's details and participants' info. It also shows where participants' details are stored: in t2 or in t3.

Basically, what I attempt to do is to compile a list of participants from t2 and t3 into one single list and need to arrange that list alphabetically.

[johanafm]

SELECT 
    t1.*, 
    CASE t2.t2LastName IS NULL THEN t3.t3LastName ELSE t2.t2lastName END as lastName

[zzz]

Bingo! This is exactly what I was looking for. The only thing that I had to add was WHEN. Works great!

SELECT 
    t1.*, 
    CASE 
        WHEN t2.t2LastName IS NULL 
        THEN t3.t3LastName 
        ELSE t2.t2lastName
    END as lastName

[ixalmida]

Yep, that's what I thought. 🙂