Simple MySQL Join problem

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?

---

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

---

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

---

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)

---

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

---

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

---

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;