MySql Query: ordering by two fields in two tables
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 sea开发者_运维技巧rch '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
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.
order by member_extra.something desc
first, which I believe will list NULLs after non-NULLs, or
order by case when member_extra.something IS NOT NULL 1 else 2 end
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
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
精彩评论