Marc Marc - 1 month ago 6
MySQL Question

How to write MySql select statment to get all defined settings or default settings not overidden for an id in one statement

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

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.

Comments