MySQL Select from table return all, plus one column with condition and another query-CodePudding

I'm pretty basic at MySQL queries. I work on a Laravel web app, at this point it comes to edit the User Role's part. I have a table that lists options for a user's role (called mst_level), and another table reflects the list of users who has that role (called mst_user_level). Been search this topic several times, but always found a different case.

mst_level

mst_user_level

Expected Output:

Select all levels for a spesific user_id, and return all columns in mst_level 1 column called "checked", with the condition if the user has that role in mst_user_level, return true, else return false.

This is what I already did, which I know it's wrong

    select a.*, coalesce(true, false) as checked from  my_db_name.mst_level a
         inner join my_db_name.mst_user_status b on a.id = b.mst_level_id
         where b.mst_user_id = '363fdeea-5330-490a-b4db-34e32a3526d6'

Anyone can help me out with this? So much Appreciated...

CodePudding user response：

You can do it with EXISTS:

SELECT m.*, 
       EXISTS (
         SELECT 1 
         FROM my_db_name.mst_user_status s 
         WHERE s.mst_level_id = m.id
           AND s.mst_user_id = '363fdeea-5330-490a-b4db-34e32a3526d6'
       ) AS checked 
FROM my_db_name.mst_level m;

Or with a LEFT JOIN where the conditions are set in the ON clause:

SELECT m.*, s.mst_level_id IS NOT NULL AS checked 
FROM my_db_name.mst_level m LEFT JOIN my_db_name.mst_user_status s 
ON s.mst_level_id = m.id AND s.mst_user_id = '363fdeea-5330-490a-b4db-34e32a3526d6';