Stefan Stefan - 27 days ago 10
MySQL Question

Combining NOT EXISTS query and a normal SELECT query into a single statement

I have a a query in multiple tables:

SELECT s.id, st.service_type, s.service_measure, sp.id as `service_prov_id`,
sp.service_prov_name
from service s, service_prov_type_assign spta, service_prov_type_service_assign sptsa,
service_type st, service_type_assign sta, service_provider sp
WHERE sp.id = '3' AND spta.service_prov_id = sp.id AND
sptsa.service_prov_type_id = spta.service_prov_type_id AND
s.id = sptsa.service_id AND sta.service_id = s.id AND
st.id = sta.service_type_id


The result of the query are as follows:

id | service_type | service_measure | service_prov_id | service_prov_name
--------------------------------------------------------------------------
7 | a | Cleaning | 1 | Alex
8 | b | Driving | 1 | Alex
9 | c | Writing | 1 | Alex


I want to apply a NOT EXISTS query to the above query:

SELECT s.id, s.service_measure
FROM first_query
WHERE not exists (Select 1
from user_service_hist ush
where ush.service_id = s.id)


My
user_service_hist ush
table looks as follows:

id | service_id
--------------------
1 | 7
2 | 8


Basically, the results of the first query should only show services that have not been added to
user_service_hist ush
table yet.

I would appreciate some pointers on how to combine these 2 queries into a single statement.

I expect my result to look as follows:

id | service_type | service_measure | service_prov_id | service_prov_name
-------------------------------------------------------------------------
9 | c | Writing | 1 | Alex

Answer

In this case just simply add the 2nd query's where clause to the 1st query's using and operator:

SELECT s.id, st.service_type, s.service_measure, sp.id as `service_prov_id`,
sp.service_prov_name
from service s, service_prov_type_assign spta, service_prov_type_service_assign sptsa, 
service_type st, service_type_assign sta, service_provider sp 
WHERE sp.id = '3'
    AND spta.service_prov_id = sp.id
    AND sptsa.service_prov_type_id = spta.service_prov_type_id
    AND s.id = sptsa.service_id
    AND sta.service_id = s.id
    AND st.id = sta.service_type_id
    AND not exists (Select 1 
          from user_service_hist ush 
          where ush.service_id = s.id)

Note: I would use the explicit join syntax instead of the implicit one to separate the join conditions from the filtering conditions. Example:

...
from service s
inner join service_prov_type_service_assign on s.id = sptsa.service_id
...

Obviously, you need to remove s.id = sptsa.service_id condition from the where clause.

Comments