1

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?

  • You could maybe emulate it using UNION and JOIN to orig table with these conditions. – jkavalik Jul 22 '15 at 7:36
  • @jkavalik can you elaborate on that? – shiznatix Jul 22 '15 at 7:40
  • can you prepare sqlfiddle with some sample data? Few rows which will show what you want, for some constant $c_id – jkavalik Jul 22 '15 at 7:46
  • sqlfiddle.com/#!9/80555b/2 the result should include column id 2 as well. Basically if the supplied c_id has a parent, then I would like all other rows that have the same parent to be returned as well. – shiznatix Jul 22 '15 at 7:56
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.