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?
 
chapmandewConnect With a Mentor Commented:
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
 
Daniel WilsonConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.