How to select all records in Table A that are not referenced for specific record in Table B-CodePudding

I am trying to select all data belonging to playlisticons table except for data that user Tom also have in playlist table.

Edit: I also have other users in the playlist table and hence I only want to return all playlisticons except for those that user Tom already have.

My code looks something like this:

    
SELECT 
playlisticons.name
,playlisticons.link 
FROM 
playlisticons 
LEFT JOIN playlist ON 
 playlisticons.name != playlist.icon WHERE playlist.userFK = 'Tom';

playlisticons.name and playlist.icon shares the same value playlisticons:

name	link
Heart	link1
Bookmark	link2
Like	link3

playlist:

playlistID	name	userFK	icon
1	Favourite	Tom	Heart
2	Watchlist	Tom	Bookmark

Current output:

name	link
Heart	link1
Bookmark	link2
Like	link3
Like	link3

Expected output:

name	link
Like	link3

CodePudding user response：

SELECT
    playlisticons.name,
    playlisticons.link
FROM playlisticons
WHERE
    playlisticons.name NOT IN (
        SELECT
            icon
        FROM playlist
        WHERE userfk = 'Tom'
    )
;

CodePudding user response：

SELECT NAME, LINK FROM PLAYGROUND.PLAYLISTICONS 
LEFT JOIN PLAYGROUND.PLAYLIST ON PLAYLISTICONS.name = PLAYLIST.ICON 
WHERE PLAYGROUND.PLAYLIST.USERFK IS NULL;