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 |
+---------+-----------+
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.
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)
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.
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