0

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
0

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;
  • Thank you! I will go for the check count() > 0 in php because anyway it will only be 1 or 0 since i have the ip_address column as unique. – Badr Jul 11 at 10:37
0
SELECT usl.*,
       EXISTS( SELECT 1 FROM blacklisted_ips
                        WHERE ip_address = usl.ip_address
             ) as blacklisted
    FROM user_session_logs as usl

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.