SQL Syntax Challenge

I have two tables, one containing a list of different options users can select from. For example:

tbl_options

  id_option

  option

The next table I use to store which of these options the user selects. For example:

tbl_selected

  id_selected

  id_option

  id_user

I use PHP to loop through the tbl_options table to generate a full list of checkboxes that the user can select from. When a user selects an option, the id_option and id_user are stored in the tbl_selected table. When a user deselects an option, the id_selected record is deleted from the tbl_selected table.

The challenge I开发者_开发技巧 am having is the best way to retrieve the full list of options in tbl_options, plus having the query indicate the associated records stored in the tbl_selected table.

I've tried LEFT JOIN'ing tbl_options to tbl_selected which provides me with the full list of options, but as soon as I add the WHERE id_user = ### the query only returns those records with values in tbl_selected. Ideally, I would like to see the results from a query as follows:

id_option  option        id_user

1              Apples        3

2              Oranges     3

3              Bananas        

4              Pears        

5              Peaches     3

This would indicate that user #3 has stored Apples, Oranges and Peaches. This also indicates that user #3 has not selected Bananas or Pears.

Is this possible using a SQL statement or should I pursue a different technique?

Your problem is that the user-restriction is applied to the whole query. To apply it only to the Join condition you need to add it to the ON clause like this:

select o.id_option, o.[option], s.id_user
from tbl_options o
    left outer join tbl_selected s
        on o.id_option = s.id_option and s.id_user = 3