April 18, 2013 at 5:08 am
Hi,
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.
Account: acct_id,acct_name
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.!!:hehe:
Thanks!
April 18, 2013 at 5:23 am
Just a hunch given I can't know the actual data, but it could be that you need left/right joins rather than inner ones. The query you use will only return rows if all the conditions match for all tables. If one table has no matching rows, then the query will not return anything.
Worth a look anyway.
Duncan
April 18, 2013 at 5:57 am
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)
April 18, 2013 at 7:08 am
Please read the first article I reference below in my signature block regarding asking for help. It will walk you through the things you need to post and how to post them.
It is really hard to help you with your query when we can't see what you see. You are going to get nothing more than shots in the dark that may or may not help. Giving us the DDL, sample data, and expected results based on the sample will go a long way in helping us help you.
April 18, 2013 at 7:40 am
kritika (4/18/2013)
well i tried that also it didnt workwhat 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)
Again just a guess (see Lynn's point above) but you could try:
join account a on (a.acct_id = ehm.acct_id
or a.acct_id = ehs.acct_id or a.acct_id = ehe.acct_id)
As I say, just a guess based on what you've said so far. If that doesn't work, I think you'll need to provide some example data. Also, even if it returns rows, they may not necessarily be the ones you want or expect!
Given the way the query appears to work, there may be database design issues to look into as well, but that's another topic perhaps.
Duncan
April 19, 2013 at 4:37 am
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!
April 19, 2013 at 4:50 am
Thanks for posting back with the solution.
Please do not use the NOLOCK hint unless you completely understand the potential consequences. If in doubt, leave it out.
John
April 19, 2013 at 5:27 am
Glad you got it figured out.
Duncan
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply