Skip to content

Instantly share code, notes, and snippets.

@jyotendra
Created Jul 14, 2018
Embed
What would you like to do?
Usage of case statement to flag user_type and sort datasets
SELECT
*
FROM
(
SELECT
USER.id AS fetched_id,
USER.country_code,
USER.mobile,
USER.email,
user_profile.name,
CASE WHEN(
SELECT
COUNT(*)
FROM
user_contact
WHERE
user_contact.user_id = 2 AND user_contact.follower_id = fetched_id
) > 0 THEN "follower" WHEN(
SELECT
COUNT(*)
FROM
user_contact
WHERE
user_contact.user_id = fetched_id AND user_contact.follower_id = 2
) > 0 THEN "following" ELSE "unrelated"
END AS user_type
FROM
USER
JOIN user_profile ON user_profile.user_id = USER.id
WHERE
USER.id IN(
SELECT
follower_id AS id
FROM
user_contact AS uc
WHERE
uc.user_id = 2 AND uc.is_friend = 1
UNION
SELECT
user_id AS id
FROM
user_contact AS uc
WHERE
uc.follower_id = 2 AND uc.is_friend = 1
)
UNION
SELECT
USER.id AS fetched_id,
USER.country_code,
USER.mobile,
USER.email,
user_profile.name,
CASE WHEN(
SELECT
COUNT(*)
FROM
user_contact
WHERE
user_contact.user_id = 2 AND user_contact.follower_id = fetched_id
) > 0 THEN "follower" WHEN(
SELECT
COUNT(*)
FROM
user_contact
WHERE
user_contact.user_id = fetched_id AND user_contact.follower_id = 2
) > 0 THEN "following" ELSE "unrelated"
END AS user_type
FROM
USER
JOIN user_profile ON user_profile.user_id = USER.id
WHERE
USER.id NOT IN(
SELECT
follower_id AS id
FROM
user_contact AS uc
WHERE
uc.user_id = 2 AND uc.is_friend = 1
UNION
SELECT
user_id AS id
FROM
user_contact AS uc
WHERE
uc.follower_id = 2 AND uc.is_friend = 1
)
) AS usr
ORDER BY FIELD(user_type, "follower", "following", "unrelated")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment