MySQL Query for displaying all rows of one table with yes/no if matching other table?

Question

I apologize in advance for the non technincal description on this problem!

I have two tables: UsersOptions and OptionsList..

For simplicity sake, OptionsList is below:

• ID - NAME
• 1 - Red
• 2 - Blue
• 3 - Orange

UsersOptions has many rows eg;

• ID - CLIENT - OPTION
• 1 - John - Red
• 2 - John - Orange
• 3 - Mary - Red
• 4 - Jill - Blue
• 5 - Jill - Orange
• etc..

Is there a query I can run that will give me the following output? (yes/no is not essential)

John's output:

• OPTION - YES/NO
• Red - y
• Blue - n
• Orange - y

Mary's output:

• OPTION - YES/NO
• Red - y
• Blue - n
• Orange - n

This is driving me crazy! Thanks to anyone that can help!

Answer 1

You can use a CASE statement with an EXISTS sub-query:

SELECT
  NAME,
  CASE WHEN EXISTS 
       (SELECT ID FROM UsersOptions 
        WHERE CLIENT = 'John' AND `OPTION` = OptionsList.NAME)
       THEN 'y' ELSE 'n' END AS `YES/NO`
FROM
  OptionsList

Answer 2

SELECT
    o.NAME,
    CASE WHEN u.OPTION IS NULL THEN 'N' ELSE 'Y' END AS 'yes/no'
FROM
    OptionsList o
LEFT OUTER JOIN
    UsersOptions u
ON
    o.NAME = u.OPTION
WHERE
    u.CLIENT = 'John'

Answer 3

This would work for John (adjust or remove WHERE clause as you see fit)

    SELECT t2.name AS name,
           IF(t1.option IS NULL, 'y', 'n') AS value
      FROM UserOptions AS t1
RIGHT JOIN OptionsList AS t2 ON (t1.option = t2.name)
     WHERE t1.client = 'John'

Though I do not understand why you did not normalize the option field in your UserOptions table. You went with the name value rather than id, for what reason?