• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

SQL Query Syntax question

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
sbornstein2
Asked:
sbornstein2
3 Solutions
 
corvetteguruCommented:
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
 
Brian CroweDatabase AdministratorCommented:
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
 
corvetteguruCommented:
Oooooh... darn... missed the extra table Document...
0
 
PortletPaulCommented:
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
 
sbornstein2Author Commented:
Case is the best way to go my opinion.  Thanks all
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now