I want a select that if/exists it returns the 'link' value instead of regular output. so instead of '1' it returns the 'link'

Is that possible?

SELECT IF(
    EXISTS(
        SELECT link FROM modules WHERE module='license'
    ), 1, '/dashboard/'
)

CodePudding user response：

Use aggregation with MAX() (or MIN()):

SELECT COALESCE(MAX(link), '/dashboard/') link
FROM modules 
WHERE module = 'license';

If that specific link does not exist in modules then MAX() will return null in which case COALESCE() will return '/dashboard/'.

CodePudding user response：

@forpas's solution is correct, but selecting MAX(link) can become slow if your table is large. We do not need to compute the maximum, since we are only interested in the existence of the link satisfying the condition.

This is a more complicated, but quicker solution:

SELECT COALESCE(t2.link, '/dashboard/')
FROM
(
    SELECT 1 AS foo
) t
LEFT JOIN (
    SELECT link 
    FROM modules 
    WHERE module='license' AND
          (NOT (link IS NULL))
    LIMIT 0, 1
) t2
ON 1 = 1

Explanation:

• we left join a dummy table with a generated relation that has the links you need if they exist
• t is a dummy table and serves the purpose to have a result even if t2 is empty
• t2 will have 0 records if there is no record meeting the criteria
• t2 will have all the links you need if at least a record is meeting the criteria
• if there are multiple records meeting the criteria and you need them all, then you can remove the LIMIT clause from t2
• the LIMIT makes sure that your search stops when you find the first match instead of searching for a maximum