Solved

SQL Query Syntax question

Posted on 2013-11-15
5
237 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

679 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