MySQL – Select Additional Rows if Initial Query Returns Specific Column Value

MySQL

I have this sample DB table:

id  type    c_owner_id  c_linked_id
1   parent  1           2
2   parent  1           3
3   related 2           3

My script is provided a c_id. I SELECT out all rows where this c_id is the c_owner_id OR c_linked_id.

What I would like is to have in the same query an additional SELECT if the c_linked_id equals the provided c_id AND type equals 'parent' that would select the rows that have a relationship with the same parent.

Pseudo SQL would be:

SELECT * FROM
    table_name AS orig
WHERE
    orig.c_owner_id = $c_id
    OR orig.c_linked_id = $c_id
IF
    orig.type = 'parent' AND orig.c_linked_id = $c_id
THEN
    (
        SELECT * FROM
            table_name AS sub
        WHERE
            sub.type = 'parent'
            AND sub.c_owner_id = orig.c_owner_id
    )

Is this type of "IF THEN SELECT" statement possible in MySQL?

Best Answer

Conditional inclusion of rows can be emulated using UNION and JOIN to the original table:

http://sqlfiddle.com/#!9/80555b/5

SELECT
    orig.*
FROM
    relationships AS orig
WHERE
    c_owner_id = $c_id
    OR c_linked_id = $c_id

-- this will add some additional rows where orig rows would match the IF cond
UNION

SELECT
    sub.*
FROM
    relationships AS sub
JOIN
    relationships AS orig ON sub.c_owner_id = orig.c_owner_id
WHERE
    sub.type = 'parent'
    AND orig.type = 'parent'
    AND orig.c_linked_id = $c_id;

You can define specific ordering for the added rows to come after the right "main" rows. You can only order the resultset of entire UNION as ORDER BY in unioned parts has no meaning.

Related Question