Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query Syntax question

Posted on 2013-11-15
5
Medium Priority
?
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

610 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