Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with joins Expand / Collapse
Author
Message
Posted Thursday, April 18, 2013 5:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 11, 2013 10:36 PM
Points: 31, Visits: 335
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.!!

Thanks!


Post #1443741
Posted Thursday, April 18, 2013 5:23 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 4:47 AM
Points: 3,352, Visits: 1,480
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
Post #1443749
Posted Thursday, April 18, 2013 5:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 11, 2013 10:36 PM
Points: 31, Visits: 335
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)
Post #1443767
Posted Thursday, April 18, 2013 7:08 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:35 PM
Points: 22,992, Visits: 31,471
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1443824
Posted Thursday, April 18, 2013 7:40 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 4:47 AM
Points: 3,352, Visits: 1,480
kritika (4/18/2013)
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)


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
Post #1443866
Posted Friday, April 19, 2013 4:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 11, 2013 10:36 PM
Points: 31, Visits: 335
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.

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!
Post #1444301
Posted Friday, April 19, 2013 4:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:59 AM
Points: 5,227, Visits: 9,437
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
Post #1444303
Posted Friday, April 19, 2013 5:27 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 4:47 AM
Points: 3,352, Visits: 1,480
Glad you got it figured out.

Duncan
Post #1444318
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse