?
Solved

Basic Query Question

Posted on 2008-11-05
4
Medium Priority
?
185 Views
Last Modified: 2013-11-05
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
0
Comment
Question by:sbornstein2
  • 2
4 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 1600 total points
ID: 22889398
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
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 400 total points
ID: 22889522
Query looks OK ... afraid I don't follow where the problem is.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22889534
Sorry, Tim, I was just REALLY slow posting.

Hope you got sbornstein the solution.
0
 

Author Closing Comment

by:sbornstein2
ID: 31513669
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question