CODEWITHSUNDEEP
sqlt-sqljoin
connersz
connersz

Reputation: 1213

How can I join two tables but get results from table one where theres no entry in table two?

I have a menu table and a user favourites table. It is possible to add a favourite to a specific user in the favourites table.

I want to get a list of all the menu entries that the user doesn't have a record for in the favourites table.

I am having problems because I can never get the complete list when one of the favourites is being used by another user to the one I am testing with.

--Data
SELECT DISTINCT MM.MenuName
FROM Portal.dbo.ModuleMenu MM
LEFT OUTER JOIN dbo.UserMenuFavourites MF
ON MM.MenuId = MF.MenuItemID
AND MF.UserID = NULL

WHERE [IsFavourite?] = 1

The code above returns the full list but does not remove the ones that user one has already entered in the MF table.

Upvotes: 0

Views: 61

Answers (2)

Bohemian
Bohemian

Reputation: 424973

You need to add a condition on the user to the join:

SELECT MM.MenuName
FROM Portal.dbo.ModuleMenu MM
LEFT JOIN dbo.UserMenuFavourites MF
  ON MM.MenuId = MF.MenuItemID
  AND MF.UserID = ? -- Specify the user you're interested in here
WHERE [IsFavourite?] = 1
AND MF.UserID IS NULL -- This condition selects only non-joins

Notes:

  • You can have non-key conditions in the join condition (many folks don't realise this)
  • For left joins all column values are null, so having the condition of a joined column being null filters out all but the non-joins
  • No need for DISTINCT, because there's only ever one row from the first table when there's no join

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

The problem is that the where clause is referring to the second table in the left outer join. When there is no match, this will be NULL.

The best way to solve this is to move the condition to the on clause:

SELECT DISTINCT MM.MenuName
FROM Portal.dbo.ModuleMenu MM LEFT OUTER JOIN
     dbo.UserMenuFavourites MF
     ON MM.MenuId = MF.MenuItemID and
        MF.[IsFavourite?] = 1
WHERE MF.UserID = NULL;

The comparison to NULL can be in the where clause.

Upvotes: 0

Related Questions