SQL getting one of each entries from left table and treating existence in the right as a boolean

Question

So I have achieved the results I want, but want to know if there is a better / cleaner way to do it:

SELECT * from Services
LEFT JOIN SellerServices ON SellerServices.Type = Services.Type    
WHERE `id` =$user_id
OR
 Services.Type NOT IN
  (
    SELECT  SellerServices.Type
    FROM     SellerServices
    where id =$user_id
  )

This returns all entries from the left table (Services in this case).

I can test later whether $user_id = id field in the results to set a check box to on or off in my html output, but what I've done seems a little kludgy and convoluted.

Does anybody know a cleaner way?

Answer 1

If your query returns the expected result, here is the same query with juste few adjustments (may be more performant):

SELECT S.*
    ,CASE
       WHEN S.id = $user_id THEN 'ON'
       ELSE 'OFF'
    END AS is_user
FROM Services S
LEFT JOIN SellerServices SS ON SS.Type = S.Type
LEFT JOIN SellerServices SSU ON SSU.id = S.id
                              AND SSU.Type = S.Type
WHERE S.id = $user_id
    OR SSU.id IS NULL

Instead of using a subquery, i've added a second jointure with table SellerServices using the same conditions as your subquery. The WHERE clause is then much more readable, i check if the id is matching the user id and if not, i check if the second jointure exists.

Hope this will help you.