Can someone Improve on this Query?

Question

SELECT Services.Type, Description
    , CASE
       WHEN id = $user_id THEN 'YES'
       ELSE 'NO'
    END AS Checked
    , Details
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
  )

To Explain the Tables Involved, SellerServices is a table that describes which services a particular seller offers.

Services describes all the services.

If I was not joining these tables, a simple "select * from Services where 1" would nearly accomplish the goal of which rows to return, but as it is I have only found a subquery capable of accomplishing the task:

The second clause, where the Services.Type not in Subquery finds one each of the Services where the $user_id doesn't match, or it is null. These are the services the seller does not offer.

The first clause finds the services he offers. The case statement gives output in a format I like.

It works, but is there a way to improve it?

Answer 1

If you want to list services along with a "yes" or "no" flag indicating whether it is offered, you can do something like this:

select s.type, s.description,
       (case when exists (select 1 from SellerServices ss where ss.Type = s.Type and ss.id = $user_id)
             then 'YES'
             else 'NO'
        end) as Checked,
       s.details
from services s;

Your question doesn't specify where details comes from, so I'm guessing from services.

You can also write this as a left outer join, assuming that there are no duplicates in the SellerServices table:

select s.type, s.description,
       (case when ss.Type is not null
             then 'YES'
             else 'NO'
        end) as Checked,
       ss.details
from Services s left join
     SellerServices ss
     on ss.Type = s.Type and ss.id = $user_id;

This can handle details coming from either table.