MySql Query: ordering by two fields in two tables

Question

Hi everybody,

I have two tables storing members data

members: id, field1, field2 and field3...

members_extra: memberId, someExtraField1 and someExtraField2

members_extra.memberId is a Foreign Key referencing members.id

'members_extra' may or may not have related rows for rows in 'members'

let's say: I have 1000 members in 'members', and I have 50 rows in 'members_extra' containing extra info

now I want to search 'members' and order the results according to 'member.field2'... but I want members who have extra info (in members_extra) to be listed before those who don't have extra info

now I use member.field3 as ENUM('true','false') which indicates whether this member has extra info or not and ordering like this: ORDER BY field3 ASC, field2 ASC... this works fine, but I don't like the approach, since I have to update members.field3 every time I insert or delete something from members_extra

how to do this without using members.field3?

thank you

Answer 1

SELECT members.id, members.field1, members_extra.someExtraField1,
Ordering = CASE members_extra.memberId WHEN NULL THEN 'Z' ELSE 'A' END 
FROM members LEFT JOIN members_extra ON members.Id = members_extra.memberId
ORDER BY Ordering

Answer 2

You can include an ORDER BY on whether members_extra.memberId is present or not:

SELECT *
FROM members
LEFT JOIN members_extra
ON members.id = members_extra.memberId
ORDER BY members_extra.memberId IS NULL, members.field2

Now you don't need members.field3 and you can delete it from your schema. This will make your database closer to normalized form.

Answer 3

THANK YOU VERY MUCH

This works!!!

    SELECT * FROM members LEFT JOIN members_extra ON members.id = members_extra.memberId ORDER BY members_extra.memberId IS NULL, members.field2`enter code here`

This gives an Error:

SELECT members.id, members.field1, members_extra.someExtraField1,

Ordering = CASE members_extra.memberId WHEN NULL THEN 'Z' ELSE 'A' END FROM members LEFT JOIN members_extra ON members.Id = members_extra.memberId ORDER BY Ordering

It should be like this:

Blockquote

SELECT members.id, members.field1, members_extra.someExtraField1,

CASE members_extra.memberId WHEN NULL THEN 1 ELSE 0 END AS Ordering FROM members LEFT JOIN members_extra ON members.Id = members_extra.memberId ORDER BY Ordering

Blockquote

One strange thing though... when I use the last query I always get ordering=1.. even though there is no corresponding rows in members_extra