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

Question

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 1

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.