How to do a JOIN & WHERE Queri?

Question

MySQL

Have a request

SELECT SUM( CASE WHEN patient_gender = 'Male' THEN 1 ELSE 0 END ) Male, SUM( CASE WHEN patient_gender = 'Female' THEN 1 ELSE 0 END ) Female FROM patients WHERE patient_ur 
IN( 
    SELECT ps_ur FROM patient_services   WHERE ps_ur > 1 AND ( ps_datetime BETWEEN '2016-08-23' AND '2016-11-22' )
    AS ur JOIN ( 
        SELECT eng_ur FROM engagements WHERE eng_ur > 1 AND ( eng_datetime BETWEEN '2016-08-23' AND '2016-11-22' )) AS ur 
  )

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS ur JOIN (
SELECT eng_ur FROM engagements WHERE eng_ur > 1 AND ( eng' at line 4

if you remove

WHERE ps_ur > 1 AND ( ps_datetime BETWEEN '2016-08-23' AND '2016-11-22' )

then everything works

SELECT SUM( CASE WHEN patient_gender = 'Male' THEN 1 ELSE 0 END ) Male, SUM( CASE WHEN patient_gender = 'Female' THEN 1 ELSE 0 END ) Female FROM patients WHERE patient_ur 
IN( 
    SELECT ps_ur FROM patient_services  //как сюда правильно WHERE поставить
    AS ur JOIN ( 
        SELECT eng_ur FROM engagements WHERE eng_ur > 1 AND ( eng_datetime BETWEEN '2016-08-23' AND '2016-11-22' )) AS ur 
  )

Answer 1

I think what you need is:

WHERE ps_ur > 1 AND ( ps_datetime BETWEEN '2016-08-23' AND '2016-11-22' )

you need to specify after JOIN
something like this:

SELECT SUM( CASE WHEN patient_gender = 'Male' THEN 1 ELSE 0 END ) Male, SUM( CASE WHEN patient_gender = 'Female' THEN 1 ELSE 0 END ) Female FROM patients WHERE patient_ur 
IN( 
    SELECT ps_ur FROM patient_services 
    AS ur JOIN ( 
        SELECT eng_ur FROM engagements WHERE eng_ur > 1 AND ( eng_datetime BETWEEN '2016-08-23' AND '2016-11-22' )) AS ur
    //где-то здесь WHERE поставить 
  )

Correct sequence of commands in a query: SELECT, FROM, JOIN, WHERE...