Query to add a column depending of outcome of there columns
I have a user table 'users' that has fields like:
id
first_name
last_name
...
and have another table that determines relationships:
user_id
friend_id
user_accepted
friend_accepted
....
I would like to generate a query that selects all the users but also add another field/column say 'network_status' that depends on the values of user_accepted and fiend_accepted. For example, if user_accepted is true friend_accepted is false I want the 'network_status' field to say 'request sent'. Can I possibly do this in one query? (I would prefer not to user if/else inside the query but if that's the only way so 开发者_StackOverflow社区be it)
You will have to take a look at CASE Statement
Something like
SELECT u.id,
u.first_name,
u.last_name,
CASE
WHEN r.user_accepted = 1 AND r.friend_accepted = 0
THEN 'request sent'
ELSE 'Not sure'
END
FROM users u LEFT JOIN
relationships r ON u.id = r.user_id
精彩评论