Simple MySQL Join problem

Question

Im stumped by this simple query because its one I have not tried before.

Ive got a User table, User_Widget table and a Widget table.

A simple inner join shows me what widgets they have by joining user_widget.user_id = user.user_id.

How would I show the widgets in the Widget table that they dont have?

Answer 1

Look up WHERE NOT EXISTS with a subselect in your documentation..

Answer 2

This is a guess, (I haven't tried it), but try This:

Select Distinct u.*, Z.*
From User u
   Left Join 
        (Select u.UserId, w.* 
         From Widget w
         Where Not Exists 
              (Select * From User_Widget
               Where userId = u.UserId 
                  And widgetId = w.WidgetId)) Z
    On Z.userId = u.UserId

Answer 3

Use a CROSS JOIN and a LEFT OUTER JOIN ( this is from my MS SQL experience, but the concept should hold ).

It works like this. The sub-query gets all possible combinations of user and widget.

The LEFT OUTER JOIN brings your User_Widgets associations into play.

The IS NULL part of the WHERE CLAUSE will exclude widgets that the user does have, giving you only the ones that don't.

SELECT allpossible.User_ID, allpossible.Widget_ID FROM
(
  SELECT User_ID, Widget_ID FROM
    Users
  CROSS JOIN
    Widgets
) allpossible
LEFT OUTER JOIN
  User_Widgets uw
ON
  allpossible.User_ID = uw.User_ID
AND allpossible.Widget_ID = uw.Widget_ID
WHERE
  uw.UserID IS NULL

Answer 4

SELECT * FROM widgets WHERE id NOT IN
(
    SELECT widget_id FROM user_widgets WHERE user_id = 1
)

(where 1 is the id of the user you're interested in)

Answer 5

Thanks to Bart Janson I got the query down to:

SELECT * FROM widgets
WHERE NOT EXISTS (
SELECT * FROM widget_user
WHERE widgets.widget_id = widget_user.widget_id
AND user_id = "ID NUMBER OF PERSON YOU WANT"
)
ORDER BY RAND()
LIMIT 10

Cheers guys

Answer 6

SELECT *
FROM widgets w
LEFT OUTER JOIN user_widget uw 
ON w.id = uw.widget_id AND uw.user_id = 1 // or whatever user u want
WHERE uw.widget_id IS NULL;