CODEWITHSUNDEEP
mysqljoin
Enchanta Support
Enchanta Support

Reputation: 209

Mysql ordering by records that exist in joined table first

I am looking for a way to join 2 tables but make the results that have records in both tables appear first in ordering.

Explanation:

SELECT npcdef.name,npcdef.id, npcinfo.npcid 
from npcdef 
LEFT JOIN npcinfo ON npcinfo.npcid=npcdef.id 
WHERE npcdef.name LIKE '%$npcname%';

How can I order the results so the records that exist in npcinfo appear first, I imagine it would be some ORDER BY statement but unsure of the logic.

Thanks

Upvotes: 1

Views: 45

Answers (1)

Barmar
Barmar

Reputation: 781059

Use this ORDER BY clause:

ORDER BY npcinfo.npcid IS NULL

For rows that are in npcinfo, npcid will not be NULL, so npcinfo.npcid IS NULL will be false, which is 0. For rows that are not in npcinfo, npcid will be NULL, so this expression will be true, which is 1.

Upvotes: 2

Related Questions