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 12»»

Outer Join Expand / Collapse
Author
Message
Posted Friday, January 11, 2008 6:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 14, 2011 2:59 AM
Points: 5, Visits: 37
When attempting to select all records from one table and only those matching from a second, not all the records are being selected from the left outer join table. Has anyone any ideas please?
Post #441707
Posted Friday, January 11, 2008 7:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 40,400, Visits: 36,840
Could you post the code please


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #441750
Posted Friday, January 11, 2008 7:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 14, 2011 2:59 AM
Points: 5, Visits: 37
Please see code below:

SELECT TOP 100 PERCENT dbo.genpremloc.level_2, dbo.genpremloc.level_5, dbo.genpremloc.gen_code, dbo.genpremloc.app_budg,
dbo.genpremloc.adj_budg, dbo.genpremloc.rev_budg, dbo.vwCrystalEstPremisesReport.LineValue, dbo.vwCrystalEstPremisesReport.OrdDate
FROM dbo.genpremloc LEFT OUTER JOIN
dbo.vwCrystalEstPremisesReport ON dbo.genpremloc.gen_code = dbo.vwCrystalEstPremisesReport.gen_code
WHERE (dbo.genpremloc.level_5 LIKE '5075%') AND (dbo.vwCrystalEstPremisesReport.OrdDate >= CONVERT(DATETIME, '2006-08-01 00:00:00', 102)) AND
(dbo.vwCrystalEstPremisesReport.OrdDate <= CONVERT(DATETIME, '2007-07-31 00:00:00', 102))
ORDER BY dbo.genpremloc.level_5, dbo.genpremloc.level_2
Post #441757
Posted Friday, January 11, 2008 5:28 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 1, 2014 5:51 AM
Points: 242, Visits: 940
Without knowing more about the interrelationship of the fields and tables, my initial guess would be your WHERE clause is limiting the result set?

Toni
Post #442043
Posted Friday, January 11, 2008 7:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 35,552, Visits: 32,149
Exactly...

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #442067
Posted Friday, January 11, 2008 10:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:39 PM
Points: 7,152, Visits: 15,634
This is one of those cases where your WHERE clause is turning your OUTER into an INNER....

You're only allowing for non-null "right" records (based on the WHERE clause, so it's not being treated as an outer join anymore....


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #442093
Posted Monday, January 14, 2008 1:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 14, 2011 2:59 AM
Points: 5, Visits: 37
Thanks to all.

:)
Post #442333
Posted Wednesday, January 16, 2008 4:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 1, 2014 5:51 AM
Points: 242, Visits: 940
There was an article in the newsletter about Derived Tables that offers a way to resolve your issue by taking the WHERE clause and creating a Derived Table for the subset of rows that meet the Where clause conditions then doing the JOIN on that subset. In that way you would get OUTER JOIN to return unmatched rows as you eliminated the filtering of the WHERE clause.

http://www.sqlservercentral.com/articles/DerivedTables/61388/

I am not totally sure but my understanding of the article would indicate the changed query would be similar to this (you will need to verify but the concept is there).

SELECT TOP 100 PERCENT dbo.genpremloc.level_2, dbo.genpremloc.level_5, dbo.genpremloc.gen_code, dbo.genpremloc.app_budg,
dbo.genpremloc.adj_budg, dbo.genpremloc.rev_budg, Derived.vwCrystalEstPremisesReport.LineValue, Derived.vwCrystalEstPremisesReport.OrdDate

FROM dbo.genpremloc LEFT OUTER JOIN

/* Derived table that covers the WHERE clause */
(SELECT dbo.genpremloc.level_5, dbo.vwCrystalEstPremisesReport.OrdDate,
dbo.vwCrystalEstPremisesReport.OrdDate
FROM dbo.genpremloc JOIN
dbo.vwCrystalEstPremisesReport ON dbo.genpremloc.gen_code = dbo.vwCrystalEstPremisesReport.gen_code
WHERE (dbo.genpremloc.level_5 LIKE '5075%') AND (dbo.vwCrystalEstPremisesReport.OrdDate >= CONVERT(DATETIME, '2006-08-01 00:00:00', 102)) AND
(dbo.vwCrystalEstPremisesReport.OrdDate <= CONVERT(DATETIME, '2007-07-31 00:00:00', 102))) as Derived




ON dbo.genpremloc.gen_code = Derived.vwCrystalEstPremisesReport.gen_code

ORDER BY dbo.genpremloc.level_5, dbo.genpremloc.level_2

Toni
Post #443531
Posted Wednesday, January 16, 2008 4:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 14, 2011 2:59 AM
Points: 5, Visits: 37
Toni thanks for that I will give it a try.
Post #443533
Posted Thursday, January 17, 2008 4:42 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 9:22 AM
Points: 216, Visits: 1,372
Hi Lyn,

With regards to Toni's link and Derived Tables, please read the discussion area too as there are some errors in what was said in the article. Not saying its not the right way, as it might be, but be aware of the discussion points and test, test, test... :)

HTH
Post #444223
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse