left outer join not returning all results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

left outer join not returning all results

Author

Topic

blackX
Posting Yak Master

102 Posts

Posted - 2008-10-08 : 14:26:36

I have a query that is joined to itself. It is a left join but it only returns records like it is an inner join, but I need to see the results in the left talbe that don't have a match to the right table.

Here is the query:

SELECT o.ContactStatus AS 'Trails3ContactStatus', o.RefusalDescription AS 'Trails3RefusalDescription', o.ContactDate AS 'Trails3ContactDate',
o.PrimaryMarketGroup AS 'Trails3MarketGroup', o.PrestnStatus AS 'Trails3PrestnStatus', o.SalesRep AS 'Trails3SalesRep',
o.GrossSalePrice AS 'Trails3SalePrice',
CASE WHEN O.phone1prime = 1 THEN O.phone1client ELSE CASE WHEN O.phone2prime = 1 THEN O.phone2client ELSE CASE WHEN O.phone3prime =
1 THEN O.phone3List ELSE CASE WHEN O.phone4prime = 1 THEN O.phone4Research ELSE CASE WHEN O.phone5prime = 1 THEN O.phone5other ELSE
CASE WHEN O.phone6prime = 1 THEN O.phone6other END END END END END END AS 'Trails3PrimaryPhone', o.PrestnDate AS 'Trails3TourDate',
o.Email AS 'Trails3Email', o.Member_Id AS 'Trails3Member_ID', o.Equity AS 'Trails3Equity', o.ContactType AS 'Trails3ContactType',
o.PrimaryID AS 'Trails3PrimaryID', n.PrimaryID AS 'NewTrailsPrimaryID', n.ContactStatus AS 'NewTrailsContactStatus',
n.RefusalDescription AS 'NewTrailsRefusalDescription', n.ContactDate AS 'NewTrailsContactDate', n.PrimaryMarketGroup AS 'NewTrailsMarketGroup',
n.PrestnStatus AS 'NewTrailsPrestnStatus', n.SalesRep AS 'NewTrailsSalesRep', n.GrossSalePrice AS 'NewTrailsSalePrice',
CASE WHEN N .phone1prime = 1 THEN N .phone1client ELSE CASE WHEN N .phone2prime = 1 THEN N .phone2client ELSE CASE WHEN N .phone3prime
= 1 THEN N .phone3List ELSE CASE WHEN N .phone4prime = 1 THEN N .phone4Research ELSE CASE WHEN N .phone5prime = 1 THEN N .phone5other
ELSE CASE WHEN N .phone6prime = 1 THEN N .phone6other END END END END END END AS 'NewPrimaryPhone',
n.PrestnDate AS 'NewTrailsTourDate', n.Email AS 'NewTrailsEmail', n.ContactType AS 'NewTrailsContactType'
FROM dbo.Member AS o LEFT OUTER JOIN
dbo.Member AS n ON o.PrimaryID = n.PrimaryID
WHERE (n.ProjectID = 'New Trails') AND (o.ProjectID = 'Trails3')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-08 : 14:40:42

FROM dbo.Member AS o LEFT OUTER JOIN
dbo.Member AS n ON o.PrimaryID = n.PrimaryID
AND (n.ProjectID = 'New Trails') WHERE (o.ProjectID = 'Trails3')

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 15:09:44

see this for explanation

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

blackX
Posting Yak Master

102 Posts

Posted - 2008-10-08 : 15:11:27

thanks it worked. I need this join on o.PrimaryID = n.PrimaryID OR
o.primaryID = substring(n.newcontractid,1,3)+substring(n.newcontractid,5,2)+substring(n.newcontractid,8,4). However it takes forever to run. The anticipated result set is 100,000 records but I cancelled the query after 2 minutes and it had only returned 217 records. My question is, how can I make it more efficient.

blackX
Posting Yak Master

102 Posts

Posted - 2008-10-08 : 15:14:29

quote:
Originally posted by visakh16

see this for explanation

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

very helpful...thanks I do still need help in the above post

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-08 : 15:41:15

An OR in the JOIN is slow.
And on top of that put a JOIN with calculated value is even slower, becuase no index can be used.

E 12°55'05.63"
N 56°04'39.26"

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-08 : 15:46:45

alter table Member add cmp_primaryID as substring(newcontractid,1,3)+substring(newcontractid,5,2)+substring(newcontractid,8,4);

create index inx_memberID on Member(cmp_primaryID);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources