Solved

SQL Query Syntax question

Posted on 2013-11-15
5
225 Views
Last Modified: 2013-11-18
Hello all,

I have a table Loan that has LoanId.   I then have a table LoanDocument with FK LoanId, FK DocumentId and Sequence (int).  Last I have a table Document with DocumentId PK and DocumentContent field.   Each loan can have up to 3 documents and the sequence will be 1, 2 or 3.   I need to get all 3 DocumentContent fields if they exist into one row for a loan but there may only be for example 2 sequence 1, 2 records.  Example:

Table Loan:
LoanId = 1

Table LoanDocument:
Records
LoanId      DocumentId     Sequence
1               111                      1
1               222                      2
1               333                      3

Table Document
Records
DocumentId      DocumentContent
111                    Content 1
222                    Content 2
333                    Content 3

I want in my row
LoanId        DocumentContent1    DocumentContent2    DocumentContent3
1                 Content 1                   Content 2                    Content 3
0
Comment
Question by:sbornstein2
5 Comments
 
LVL 2

Assisted Solution

by:corvetteguru
corvetteguru earned 30 total points
ID: 39652540
This is one way...

select l.LoanID ld1,Content, ld2.Content, ld3.Content from Loan l
Inner join LoanDocument ld1 on l.loanID = ld1.loanID and ld1.documentsequence = 1
Inner join LoanDocument ld2 on l.loanID = ld2.loanID and ld2.documentsequence = 2
Inner join LoanDocument ld3 on l.loanID = ld3.loanID and ld3.documentsequence = 3
0
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 30 total points
ID: 39652590
SELECT L.LoanID,
   D1.DocumentContent AS DocumentContent1,
   D2.DocumentContent AS DocumentContent2,
   D3.DocumentContent AS DocumentContent3
FROM Loan AS L
LEFT OUTER JOIN LoanDocument AS LD1
   INNER JOIN Document AS D1
      ON LD1.DocumentID = D1.DocumentID
   ON L.LoanID = LD1.LoanID
   AND LD1.Sequence = 1
LEFT OUTER JOIN LoanDocument AS LD2
   INNER JOIN Document AS D2
      ON LD2.DocumentID = D2.DocumentID
   ON L.LoandID = LD2.LoadID
   AND LD2.Sequence = 2
LEFT OUTER JOIN LoanDocument AS LD3
   INNER JOIN Document AS D3
      ON LD3.DocumentID = D3.DocumentID
   ON L.LoanID = LD3.LoanID
   AND LD3.Sequence = 3
0
 
LVL 2

Expert Comment

by:corvetteguru
ID: 39652614
Oooooh... darn... missed the extra table Document...
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 100 total points
ID: 39652682
alternatively
SELECT
      L.LoanID
    , CASE WHEN LD.sequence = 1 THEN D.DocumentContent END AS DocumentContent1
    , CASE WHEN LD.sequence = 2 THEN D.DocumentContent END AS DocumentContent2
    , CASE WHEN LD.sequence = 3 THEN D.DocumentContent END AS DocumentContent3
FROM Loan AS L
LEFT JOIN LoanDocument AS LD ON L.LoanID = LD.LoanID
INNER JOIN Document AS D ON LD.DocumentID = D.DocumentID
GROUP BY
      L.LoanID
;

Open in new window

0
 

Author Closing Comment

by:sbornstein2
ID: 39656498
Case is the best way to go my opinion.  Thanks all
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now