Basic Query Question

Hello all.  How do I handle this.  I am doing a Left Join to a table.  I need to no matter what in the below query pull all the records from ReportDisplayOrder where ReportID = 9 even if the LineID in ReportDisplayOrder = Null.   If it is now null then I pull data from the InvestmentAllocationSummary table.  Any help?


SELECT InvestmentAllocationSummary.*, HeaderText, Investment.[Name], LineText
       FROM ReportDisplayOrder LEFT JOIN LineItem ON ReportDisplayOrder.LineID = LineItem.LineID
            LEFT JOIN InvestmentAllocationSummary ON LineItem.LineID = InvestmentAllocationSummary.LineID
            LEFT JOIN Investment ON InvestmentAllocationSummary.InvestmentID = Investment.InvestmentID
            LEFT JOIN TaxPeriod ON InvestmentAllocationSummary.TaxPeriodID = TaxPeriod.TaxPeriodID
            LEFT JOIN Partnership ON InvestmentAllocationSummary.FundPartnershipID = Partnership.PartnershipID
         WHERE ReportDisplayOrder.ReportID=9 AND InvestmentAllocationSummary.TaxPeriodID=1
              AND InvestmentAllocationSummary.FundPartnershipID=6 AND
                    InvestmentAllocationSummary.InvestmentID = 16
         ORDER BY InvestmentAllocationSummary.InvestmentID,ReportDisplayOrder.SortOrder
sbornstein2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
try this:

SELECT InvestmentAllocationSummary.*, HeaderText, Investment.[Name], LineText
       FROM ReportDisplayOrder LEFT JOIN LineItem ON ReportDisplayOrder.LineID = LineItem.LineID
            LEFT JOIN InvestmentAllocationSummary ON LineItem.LineID = InvestmentAllocationSummary.LineID AND InvestmentAllocationSummary.TaxPeriodID=1 AND InvestmentAllocationSummary.FundPartnershipID=6  InvestmentAllocationSummary.InvestmentID = 16
            LEFT JOIN Investment ON InvestmentAllocationSummary.InvestmentID = Investment.InvestmentID
            LEFT JOIN TaxPeriod ON InvestmentAllocationSummary.TaxPeriodID = TaxPeriod.TaxPeriodID
            LEFT JOIN Partnership ON InvestmentAllocationSummary.FundPartnershipID = Partnership.PartnershipID
         WHERE ReportDisplayOrder.ReportID=9                  
         ORDER BY InvestmentAllocationSummary.InvestmentID,ReportDisplayOrder.SortOrder
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Daniel WilsonCommented:
Query looks OK ... afraid I don't follow where the problem is.
0
Daniel WilsonCommented:
Sorry, Tim, I was just REALLY slow posting.

Hope you got sbornstein the solution.
0
sbornstein2Author Commented:
actually the best way I remember to do it was in the Where clause have OR is null for the fields other than the reportdisplayorder.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.