CODEWITHSUNDEEP
mysqlsql
toastext
toastext

Reputation: 335

Select different column in a row 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 a lot 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:

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 statement?

Upvotes: 1

Views: 299

Answers (1)

e4c5
e4c5

Reputation: 53774

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

Upvotes: 1

Related Questions