SQL Query - select one if exists else other, never both

Question

Let's say there is a table with two columns, First Name and Eye Color.

In this table you could potentially have:

Name - Green Eyes
Name - Brown Eyes

I'm trying to select all the rows for names with green eyes, but if there is a name that doesn't have green eyes, then I would like to select the name with Brown eyes. I never want to return two rows for a given name. Any thoughts on how to do this? Any help would be greatly appreciated!

Answer 1

SELECT COALESCE(g.name, b.name) name,
    COALESCE(g.eye_color, b.eye_color) eye_color

FROM (
  SELECT DISTINCT name, eye_color
  FROM eye_colors
  WHERE eye_color = 'green'
) g

FULL OUTER JOIN (
  SELECT DISTINCT name, eye_color
  FROM eye_colors
  WHERE eye_color = 'brown'
) b
ON b.name = g.name

Answer 2

IF EXISTS(SELECT * FROM Table WHERE Color='Green')
    SELECT * FROM Table WHERE Color = 'Green'
ELSE
    SELECT * FROM Table WHERE Color='Brown'