Select value based on a test

Question

I have two tables user_session_logs and blacklisted_ips. The first one for logging access logs and the second one is for blacklisted ips, and both tables have a column named ip_address.

I need to select all columns from the first table and another field 'blacklisted' that tells if the ip in that access log is blacklisted or not.

I used a left join and COUNT() function to count occurences for each ip address but this will return a number, while i just need a boolean.

Here is my SQL code:

SELECT usl.*, COUNT(blip.id) as blacklisted
FROM user_session_logs as usl
LEFT JOIN blacklisted_ips as blip ON blip.ip_address = usl.ip_address
GROUP BY usl.id

Answer 1

Use a CASE if you want, but you can always check if COUNT() > 0

SELECT usl.*, 
    CASE WHEN COUNT(blip.id) > 0 THEN TRUE ELSE FALSE END as blacklisted
FROM user_session_logs as usl
LEFT JOIN blacklisted_ips as blip ON blip.ip_address = usl.ip_address
GROUP BY usl.id;

Answer 2

SELECT usl.*,
       EXISTS( SELECT 1 FROM blacklisted_ips
                        WHERE ip_address = usl.ip_address
             ) as blacklisted
    FROM user_session_logs as usl