Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Query Syntax question

Posted on 2013-11-15
5
Medium Priority
?
248 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 120 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 120 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 49

Accepted Solution

by:
PortletPaul earned 400 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

810 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