well i tried that also it didnt work

what i noticed after an hr of R&D is

select * 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

where ehl.created_date > (dateadd("day",-7,getdate()))

-- and log_response like '5.3.0 %Rejected - see%interland%'

i.e. if i remove account join the query returns me the records

additionally as per you thot, ehs and ehm doesnot contain data when joined seperately.its only ehe that retrives data when joined alone

but data might be present in production, so i want a single query to retrieve data from all these 3 tables in one shot.

so the logic lies in join to account table.

my problem how do i join these tables with account table having different acct_id's for ehs,ehm,ehe tables.

can something like this be accomplished:-

join account a on a.acct_id in ( ehm.acct_id,ehs.acct_id,ehe.acct_id)