alright.. so here you go..

I gotthe solution though while I was creating this test data. but this is just for the refrence for everybody who visited the post and merely left because of lack of data 😉

CREATE TABLE email_history_log

(

message_stub UNIQUEIDENTIFIER

)

CREATE TABLE event_email_history

(

acct_id INT,

message_stub UNIQUEIDENTIFIER

)

CREATE TABLE survey_email_history

(

acct_id INT,

message_stub UNIQUEIDENTIFIER

)

CREATE TABLE emarketing_email_history

(

acct_id INT,

message_stub UNIQUEIDENTIFIER

)

CREATE TABLE account

(

acct_id INT,

acct_name NVARCHAR(50)

)

INSERT INTO email_history_log

SELECT NEWID()

UNION ALL

SELECT NEWID()

SELECT * FROM email_history_log

INSERT INTO event_email_history

SELECT 1,'EC09B6E6-D5DC-4FB6-9784-B9D4680A6376'

UNION ALL

SELECT 2,'4C15B39F-5EA4-46F5-B6D3-5BDACBE0E4B1'

-- these are 2 stubs same in email_log history, i created same data to have FK like structure:-P.

INSERT INTO account

SELECT 1,'Kritika'

UNION ALL

SELECT 2,'DBA'

SELECT * FROM email_history_log

SELECT * FROM event_email_history

SELECT * FROM survey_email_history

SELECT * FROM emarketing_email_history

SELECT * FROM account

select a.acct_id, a.acct_name, count(a.acct_id)

from email_history_log ehl (nolock)

LEFT join event_email_history ehe (nolock) on ehe.message_stub = ehl.message_stub

LEFT join survey_email_history ehs (nolock) on ehs.message_stub = ehl.message_stub

LEFT join emarketing_email_history ehm (nolock) on ehm.message_stub = ehl.message_stub

join account a on (a.acct_id = ehm.acct_id

or a.acct_id = ehs.acct_id or a.acct_id = ehe.acct_id)

GROUP BY a.acct_id, a.acct_name

ORDER BY count(a.acct_id) DESC

thanks again!