I need a help.
i have 5 tables defined below:-
these tables are used to record all data pertaining to emails.
Emarketing_email_history : message_stub,acct_id,....
Survey_email_history : message_stub,acct_id,....
Event_email_history : message_stub,acct_id,....
this record all data pertaining to email bounce.
Email_history_log : message_stub....
this is acct specific data.
i want to get the count of all acct's that have bounced emails.
my query below:-
select top 500 a.acct_id, a.acct_num, a.acct_company, count(a.acct_id)
from email_history_log ehl (nolock)
join email_history ehe (nolock) on ehe.message_stub = ehl.message_stub
join survey_email_history ehs (nolock) on ehs.message_stub = ehl.message_stub
join emarketing_email_history ehm (nolock) on ehm.message_stub = ehl.message_stub
join account a on a.acct_id = ehm.acct_id -----------????
where ehl.created_date > (dateadd("day",-7,getdate()))
GROUP BY a.acct_id, a.acct_num, a.acct_company
ORDER BY count(a.acct_id) DESC
but some how this doesnt retrive records. though when I remove join condition( i e removing 2 joins from join 1 ,2 or 3) I get records.
is there any way I can get my result by joining all 3 tables plus account table with event_history_log?
PS i dont want union/union all
help much appritiated.!!