SELECT(IF(IN query.

Question

There are 3 tables. Products, Options and Prod_Opts_relations. The latter holds product_id and option_id so i should be able to figure out which options are selected for any given product.

Now i want to retrieve all options from the options table where the value of an extra alias field should hold checked or unchecked depending on the existance of a mathing record in the relations table for a give product id.

Thus far i came up with this:

SELECT 
 IF(IN(SELECT id_option FROM prod_opt_relations WHERE id_product='18'),'y','n') AS booh  
 ,optionstable.id AS parent_id  
 ,optionstable.name_en AS parent_english  
 ,optionstable.name_es AS parent_spanish  
FROM product_options AS optionstable  
WHERE 1  

resulting in syntax errors.

Alas i just cannot figure out where things go wrong here

Answer 1

You did not specify which expression should be compared to your IN-list:

IF( foo IN (...), 'y', 'n')

edit: Your statement lacks an equivalent to what is foo in this example.

See http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in

Answer 2

Use a left join between your two tables to retrieve all the data from the option table whenever there is a match or not. Then test if the value return from the right table is null to set your y/n flag.

SELECT                                                                        
  IF(por.id_product IS NULL, 'n', 'y') AS booh                                      
  ,optionstable.id AS parent_id                                                
  ,optionstable.name_en AS parent_english                                      
  ,optionstable.name_es AS parent_spanish                                      
FROM
   product_options AS optionstable                                          
   LEFT JOIN prod_opt_relations as por ON (
     por.id_product=18 AND por.id_option=optionstable.id_option
   )
WHERE 1