Jeremy Jeremy - 2 days ago 4
MySQL Question

SQL statement piped to conditional splitting result returned based on conditional

(Couldn't think of a very good title for this)

Say I have a table as follows

CREATE TABLE IF NOT EXISTS accts (
name varchar(255) NOT NULL,
association varchar(255) NULL,
type varchar(255) NOT NULL,
UNIQUE (name, association)
) ENGINE=InnoDB;

mysql> desc accts;
+-------------+--------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-------------+--------------+------+-----+---------+
| name | varchar(255) | NO | MUL | NULL |
| association | varchar(255) | YES | | NULL |
| type | varchar(255) | NO | | NULL |
+-------------+--------------+------+-----+---------+


I would like to query based on name, then from the results, grab the name relevant to the association if it exists, otherwise, grab the row with
NULL
for association

Example:

association = 'hello';
results = query('SELECT * FROM accts WHERE name = "world"');

/*
results = [
{ name: 'hello', association: null },
{ name: 'hello', association: 'earth' },
{ name: 'hello', association: 'world' },
]
*/

// here I want to grab the row where association matches,
// but default to association = NULL if none exists
// (the psuedo code below describes it programmatically)

default = null
for result in results
if not result.association
default = result
else if result.association equals association
return result

return default


It is simple to write with code, but I would prefer to wrap this up into a SQL statement, can anyone help?

Answer

I think you want something like this:

SELECT *
FROM accts
WHERE name = 'world' AND (assocation = 'hello' or association IS NULL)
ORDER BY (association IS NOT NULL) DESC
LIMIT 1;

Or, alternatively:

SELECT a.*
FROM accts a
WHERE name = 'world' AND assocation = 'hello' 
UNION ALL
SELECT a.*
FROM accts a
WHERE name = 'world' AND association IS NULL AND
      NOT EXISTS (SELECT 1 FROM accts a2 WHERE a2.name = a.name)
Comments