SQL显示记录首先与查找表匹配，然后与所有其他记录匹配 [英] SQL display records matching lookup table first then all other records

问题描述

想象一下，这是一个音乐网站，我有1000首歌曲.用户可以收藏"歌曲，这些歌曲使用memberID和songID存储在查找表中.

Imagine it is a music site and I have 1000 songs. Users can "favorite" songs, this is stored in a lookup table using memberID and songID.

然后我想在所有歌曲列表的顶部显示特定用户的收藏夹.

I then want to display the favorites for a specific user at the top of a list of all songs.

SELECT  s.*, s.songID theSongID, f.*  
FROM su_songs AS s 
left JOIN su_member_favourites_lookup AS f 
ON f.songID = s.songID 
ORDER by f.songID IS NULL, s.songTitle ASC

...可以在顶部显示所有收藏夹.但是我只想显示登录用户的收藏夹，然后显示下面的所有其他歌曲

...works to display all favorites at the top. But I just want to display the logged in user's favorites and then all other songs underneath

SELECT  s.*, s.songID theSongID, f.*  
FROM su_songs AS s 
left JOIN su_member_favourites_lookup AS f 
ON f.songID = s.songID 
WHERE memberID = " . $memberID . " 
ORDER by f.songID IS NULL, s.songTitle ASC

...如果我添加WHERE子句以匹配用户，则它仅显示用户的收藏夹，而忽略其余歌曲.

...if I add the WHERE clause to match the user, it only shows the user's favorites and leaves out the rest of the songs.

推荐答案

解决方案是使用AND而不是WHERE

The solution is to use AND instead of WHERE

SELECT  s.*, s.songID theSongID, f.*  
FROM su_songs AS s 
LEFT JOIN su_member_favourites_lookup AS f 
ON f.songID = s.songID AND f.memberID = " . $memberID . " 
ORDER by f.songID IS NULL, s.songTitle ASC

这最初是一个真实的问题，然后我对可能有用的方法进行了思考……并且确实如此.希望这会对其他人有所帮助.

This started as a real question and then I had a thought on what might work...and it did. Hopefully this will help someone else.

这篇关于SQL显示记录首先与查找表匹配，然后与所有其他记录匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！