define "WHERE" for "LEFT JOIN" in an SQL query?

OK, here's yet another sql query that's giving me headaches.

Case: I'm listing a description of an auction, and the listing will also say whether the user who is lo开发者_StackOverflowgged in is watching this auction, or not (like a favourite).

If he's watching the auction, his user ID + auction ID is inside a table called watchlist-auctions. If he's not watching, the entry is not there.

Here is my query:

   SELECT
      `auctions`.`auction_description_1`,
      `watchlist-auctions`.`watchlist_a_id` as `watch-auction`
   FROM `auctions`
   LEFT JOIN `watchlist-auctions` ON `auctions`.`auction_id`=`watchlist-auctions`.`watchlist_a_auction`
   WHERE `auction_id`='6'
   GROUP BY `auctions`.`auction_id`

Basically, it works, kinda, but I don't know where in the query do I define the user ID?

I have the logged in user id in $userID (in php).

I'm not sure whether I explained this right, I'm so depressed from this, because it's such a simple problem, but I find those SQL queries so difficult I feel like crying. If I can explain this better, please, tell me where I messed up :/

P.S.: If I can offer a bounty for this, I want to offer +100 right away.

 SELECT
      `auctions`.`auction_description_1`,
      `watchlist-auctions`.`watchlist_a_id` as `watch-auction`
 FROM `auctions`
 LEFT JOIN `watchlist-auctions` ON
           `auctions`.`auction_id`=`watchlist-auctions`.`watchlist_a_auction`
       AND `watchlist-auctions`.userID= $userID
 WHERE `auction_id`='6'
 GROUP BY `auctions`.`auction_id`

So the LEFT JOIN activates on both conditions auction+user, but it does not interfere with the LEFT table (auctions).

Not completely sure where the user_id field is located in your schema, also not sure what the field name is in the table; but you should be able to add it as additional criteria to your where clause.

WHERE `auction_id`='6' AND `watchlist-auctions`.`user_id` = $userID

I'm not sure why you're left joining as every auction should be in the watch-auctions:

   SELECT
      `auctions`.`auction_description_1`,
      `watchlist-auctions`.`watchlist_a_id` as `watch-auction`
   FROM `watchlist-auctions`
   inner JOIN `auctions` ON `auctions`.`auction_id`=`watchlist-auctions`.`watchlist_a_auction`
   WHERE `auction_id`='6'
      and `user_id` = .......
   GROUP BY `auctions`.`auction_id`