How to write MySql select statement to get all def

Question

In a table myTable defined as:

+----+---------+-----------+
| id |  name   |   value   |
|----+---------+-----------+
| 7  | hand    |  right    |
| 5  | hand    |  left     |
| 0  | hand    |  both     |
| 0  | feet    |  both     |
| 0  | eyes    |  green    |
| 9  | eyes    |  blue     |
| 2  | eyes    |  white    |
| 2  | hand    |  raised   |
+----+---------+-----------+

Default settings are controlled by id = 0.

My question is how to write a select statement to get name,value for id = 5 in one query that will include set for id = 5 and any defaults not overridden.

The results should be:

+---------+-----------+
|  name   |   value   |
+---------+-----------+
| hand    |  left     |
| feet    |  both     |
| eyes    |  green    |
+---------+-----------+  

Answer 1

SELECT * FROM mytable WHERE ID = 5 
UNION
SELECT * FROM mytable WHERE ID = 0 AND name NOT IN (SELECT name FROM MyTable
WHERE id = 5)

Should get you the right answer.
http://sqlfiddle.com/#!9/1f516/14

Answer 2

The following does not return three rows in the result set, it only returns a single row with columns hand, feet and eyes, so it may not work for you. But it should return the data that you're looking for given your conditions:

SELECT (CASE 
        WHEN EXISTS(SELECT 1 FROM myTable WHERE name='hand' AND id=5)
        THEN (SELECT `value` FROM myTable WHERE name='hand' AND id=5)
        ELSE (SELECT `value` FROM myTable WHERE name='hand' AND id=0)
    END) AS hand,
    (CASE 
        WHEN EXISTS(SELECT 1 FROM myTable WHERE name='feet' AND id=5)
        THEN (SELECT `value` FROM myTable WHERE name='feet' AND id=5)
        ELSE (SELECT `value` FROM myTable WHERE name='feet' AND id=0)
    END) AS feet,
    (CASE 
        WHEN EXISTS(SELECT 1 FROM myTable WHERE name='eyes' AND id=5)
        THEN (SELECT `value` FROM myTable WHERE name='eyes' AND id=5)
        ELSE (SELECT `value` FROM myTable WHERE name='eyes' AND id=0)
    END) AS eyes
;

Output:

+---------+-----------+-----------+
|  hand   |   feet    |   eyes    |
+---------+-----------+-----------+
|  left   |  both     |   green   |
+---------+-----------+-----------+  

This is approach generates 10 different SELECT statements rather than 1, and the accepted answer is, for most applications, probably a better way to go about it.

Answer 3

It isn't clarified if the ordering of the result set is important, so might as well try:

SELECT name,
    value
FROM MyTable
WHERE id = 5
    AND id NOT IN
    (
        SELECT id
        FROM MyTable
        WHERE id = 0
        GROUP BY MyTable.id
    )
UNION
SELECT name,
    value
FROM MyTable
WHERE id = 0
    AND name NOT IN
    (
        SELECT name
        FROM MyTable
        WHERE id = 5
        GROUP BY MyTable.name
    )

Disclaimer: Tested in SQL Server, but not using anything specific to that version of SQL.

Answer 4

seems this one may work for you:

SELECT id,name,value 
FROM test  
WHERE id = 5  
UNION 
SELECT id,name,value 
FROM test 
WHERE id=0 
and name not in (select name from test where id=5)