通过已存在的MYSQL查询中的连接来连接两个表

Question

I have a structure something like this of my two tables:

z_notes:

| i_resident_id | resident_fname | resident_lname | i_communication_log | facility_id | dt_note_created | 

user_roles:

| fk_role_id | fk_user_id | 

The query that is meant to run on z_notes is working fine and perfectly well. Here it is.

"SELECT `i_resident_id` AS ID, resident_fname AS FirstName, `resident_lname` AS LastName, 
                        SUM(CASE i_communication_log WHEN 1 THEN 1 ELSE 0 END ) AS Critical_Notes, 
                        SUM(CASE i_communication_log WHEN 0 THEN 1 ELSE 0 END ) AS Routine_Notes,
                        SUM(1) AS Total_Notes
                        FROM z_notes
                        WHERE dt_note_created > '$from'
                        AND dt_note_created < '$to' AND
                        facility_id = '$facility_id'
                        GROUP BY v_resident_fname 
                        ORDER BY Total_Notes $asc_des";

This returns:

ID |    FirstName |     LastName |  Critical_Notes |    Routine_Notes |     Total_Notes

Now this is where i need a join in the query above. This is how the foreign key is worked out:

i_resident_id in z_notes which links to fk_user_id in user_roles

Hence, those residents (i_resident_id) whose role i.e. fk_role_id == 4 should be excluded from the query. That is, There needs to be more in Where clause which should be something like i_resident_id which is equal to fk_user_id has a column fk_role_id in its table which should not be '4'.

Your input will be highly appreciated. :)

Answer 1

Try:

SELECT `i_resident_id` AS ID, resident_fname AS FirstName, `resident_lname` AS LastName, 
                        SUM(CASE i_communication_log WHEN 1 THEN 1 ELSE 0 END ) AS Critical_Notes, 
                        SUM(CASE i_communication_log WHEN 0 THEN 1 ELSE 0 END ) AS Routine_Notes,
                        SUM(1) AS Total_Notes
                        FROM z_notes
                        LEFT JOIN user_roles ON z_notes.i_resident_id = user_roles.fk_user_id
                        WHERE dt_note_created > '$from'
                        AND dt_note_created < '$to'
                        AND facility_id = '$facility_id'
                        AND user_roles.fk_role_id != 4
                        GROUP BY v_resident_fname 
                        ORDER BY Total_Notes $asc_des";

I just did you said, added a join and a where clause