MYSQL JOIN WHERE ISSUES - need some kind of if condition

Question

Hi Well this will be hard to explain but ill do my best

The thing is i have 4 tables all with a specific column to relate to eachother. 1 table with users(agent_users) , 1 with working hours(agent_pers), 1 with sold items(agent_stat),1 with project(agent_pro) the user and the project table is irrelevant in the issue at hand but to give you a better understanding why certain tables is included in my query i decided to still mention them =) The thing is that I use 2 pages to insert data to the working hour and the sold items during that time tables, then i have a third page to summarize everything for current month, the query for that is as following:

SELECT *, SUM(sv_p_kom),SUM(sv_p_gick),SUM(sv_p_lunch) FROM ((
agent_users 
LEFT JOIN agent_pers ON agent_users.sv_aid = agent_pers.sv_p_uid) 
LEFT JOIN 
agent_stat ON agent_pers.sv_p_uid = agent_stat.sv_s_uid) 
LEFT JOIN 
agent_pro ON agent_pers.sv_p_pid=agent_pro.p_id
WHERE MONTH(agent_pers.sv_p_datum) =7 GROUP BY sv_aname

so the problem is now that i dont want sold items from previous months to get included in the data received, i know i could solve that by simple adding in the WHERE part MONTH(agent_stat.sv_s_datum) =7 but then if no items been sold that month no data at all will show up not the time or anything. Any aid on how i could solve this is greatly appreciated. if there's something that's not so clear dont hesitate to ask and ill try my best to answer. after all my english isn't the best out there :P regards breezer

Answer 1

Is your data such that you could add (MONTH(agent_stat.sv_s_datum) =7 OR agent_stat.sv_s_datum IS NULL) to the WHERE clause?

Answer 2

Fair enough :) -- put the condition as a second condition in your JOIN clause. ON (agent_pers.sv_p_uid = agent_stat.sv_s_uid AND agent_stat.sv_s_datum = 7)