CODEWITHSUNDEEP
mysql
Pandy Legend
Pandy Legend

Reputation: 1111

MySQL return alternative record if no record returned

I have a table like the following

-------------------------
|  aid  |  tid  |  dft  |
-------------------------
|   1   |   1   |   Y   |
-------------------------
|   1   |   2   |   N   |
-------------------------
|   2   |   3   |   Y   |
-------------------------
|   3   |   4   |   Y   |
-------------------------
|   3   |   5   |   N   |
-------------------------

I want to query the table to get the tid out based on aid and tid but if the tid does not exist for that aid then it should return the default tid (identified by Y in the dft column).

I have tried the following which does not throw an error but returns NULL

SELECT

IF (
    t1.tid IS NOT NULL,
    t1.tid,
    (
        SELECT
            t2.tid
        FROM
            table t2
        WHERE
            t2.aid = 1
        AND t2.dft = 'Y'
    )
) AS tid
FROM
    table t1
WHERE
    t1.aid = 1
AND t1.tid = 3

As that code is looking up tid 3 with aid 1 that doesn't exist so should return tid of 1 as that is what is marked as Y in the dft column.

Can anybody help?

Upvotes: 0

Views: 189

Answers (2)

PaulMolloy
PaulMolloy

Reputation: 632

This would work in MSSQL, but I dont have access to a MySQL server to try it

SELECT TOP 1 *
FROM table t1
WHERE t1.aid=1
ORDER BY CASE WHEN t1.tid=3 THEN 0 ELSE 1 END, CASE WHEN t1.dft='Y' THEN 0 ELSE 1 END

Upvotes: 0

Chandu
Chandu

Reputation: 82893

Try this:

SELECT tid
 FROM table t1
WHERE t1.aid = 1
  AND t1.tid = 3
UNION   
SELECT tid
 FROM table t1
WHERE t1.aid = 1
  AND t1.tid <> 3
  AND dft = 'Y'

If the combination of aid and tid is unique then you can use this version:

SELECT tid
 FROM table t1
WHERE t1.aid = 1
  AND (t1.tid = 3 OR dft = 'Y')
ORDER BY dft LIMIT 1

Upvotes: 1

Related Questions