Danny Danny - 4 months ago 8
SQL Question

select different coloum in a rows depending on the value

I am trying to return a friend list for my users and I'm trying to gather the value matching their user_id.

| ID | user1_id | user1_status | user2_id | user2_status |
| 1 | 1 | 0 | 2 | 0 |
| 2 | 4 | 1 | 5 | 1 |
| 3 | 4 | 1 | 6 | 0 |
| 4 | 1 | 0 | 4 | 1 |


Here is the problem I have, the value I'm look for can be in either "user1_id"/"user2_id" and then I need to return the "user_status" ONLY for the other user. n. I made this table really simple. In my version there is alot more columns I want my server to avoid returning.

Let's say that the client user_id is 4, so I need the select all the row with user1_id/user2_id equal to 4 and return the other person user_status. In the table, the first case of the value equal to 4 is in user1_id, I need that row to return the user2_id and user2_status.

Here is what I have so far, but it doesn't work:

mysqli($connect, "
SELECT `id`
CASE
WHEN `user1_id`='4' THEN `user2_id` AND `user2_status`
WHEN `user2_id`='4' THEN `user1_id` AND `user1_status`
ELSE NULL
END
from 'relationship'
where `user1_id`='4' OR `user2_id`='4'
")


How do I write this query statment?

Answer

If you refer to the CASE syntax you will see that it's defined to return a single column and not a tuple. Additionally, in your query you are trying to get either (user2_id, user_status) or NULL. Here you get a mismatch in the number of columns which is not allowed either.

If you really really want to use CASE you could do:

SELECT `id`
    CASE 
        WHEN `user1_id`='4' THEN `user2_id`, 
        ELSE NULL
    END
    CASE 
        WHEN `user2_id`='4' THEN `user1_id`
        ELSE NULL
    END
    CASE
        WHEN `user1_id`='4' THEN `user1_status`
        ELSE NULL
    END
    CASE
        WHEN `user2_id`='4' THEN `user2_status`
        ELSE NULL
    END

    FROM 'relationship' 
        where `user1_id`='4' OR `user2_id`='4'

yes, clunky and confusing. Much Simpler if you use UNION.

SELECT id, user2_id AS uid, user2_status as ustatus FROM relationship WHERE user1_id = 4
UNION
SELECT id, user1_id AS uid, user1_status as ustatus FROM relationship WHERE user2_id = 4