SQL statement requiring correction assistance. 1

[davidhexter]

In the below query, I have two fields (loan.ccmpy_id and the loan.scmpy_id) that relate to the company table's company.cmpy_id field. Pulling in the cmpy_name for the first cmpy_id was simple enough, but I am having difficulty pulling in the company name for the scmy_id field?

How do I do that?

SELECT RTRIM(LTRIM(company.cmpy_name)) AS Creditor,cloanid AS 'Creditor Loan #',scmpy_id AS 'Servicing Agent',sloanid AS [Servicer's Loan #],CONVERT(char(10),closedate,101) AS 'Closing Date',CONVERT(char(10),maturitydate,101) AS 'Maturity Date',CONVERT(char(10),balldate ,101) AS 'Balloon Payment Date'
FROM loan
INNER JOIN company ON (company.cmpy_id = loan.ccmpy_id)
WHERE maturitydate < GETDATE() AND retired <>'Y'
ORDER BY maturitydate

Thanks for your assistance.

Dave

 

[NoCoolHandle]

want to share a little sample data?

1 row from each table (only listed/key columns) might make things easier to see what is going on.


Rob

 

[hmckillop]

SELECT
RTRIM(LTRIM(cmp.cmpy_name)) AS Creditor,
lne.cloanid AS 'Creditor Loan #',
lne.scmpy_id AS 'Servicing Agent',
lne.sloanid AS [Servicer's Loan #],CONVERT(char(10),lne.closedate,101) AS 'Closing Date',
CONVERT(char(10),lne.maturitydate,101) AS 'Maturity Date',
CONVERT(char(10),lne.balldate ,101) AS 'Balloon Payment Date',
(SELECT cmp1.cmpy_name FROM Company cmp1 WHERE cmp1.scmpy_id = lne.scmpy_id) as CompanyName2
FROM loan lne
INNER JOIN company cmp ON (cmp.cmpy_id = loan.ccmpy_id)

WHERE maturitydate < GETDATE() AND retired <>'Y'
ORDER BY maturitydate

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[davidhexter]

Sure:

Loan (PK = ccmpy_id, cloanid)
ccmpy_id cloaid scmpy_id sloanid
CAAREAL 1 SAAREAL 1

Company (PK = cmpy_id)
cmpy_id cmpy_name
CAAREAL Aareal Bank AG
SAAREAL Aareal Bank France

It is simply a coincidence that the Creditor's Company Id is similar to the Servicing Agent's company id.

 

[davidhexter]

Thank you hmckillop. I forgot that I can add a SQL statement in place of a column (Back to basics!!)

Dave

 

[ESquared]

davidhexter,

I think the following construction is more straightforward and could possibly offer some performance benefits:

SELECT 
   Creditor               = RTRIM(LTRIM(cmp1.cmpy_name)),
   'Creditor Loan #'      = lne.cloanid,
   'Servicing Agent'      = lne.scmpy_id,
   'Servicer''s Loan #'   = lne.sloanid,
   'Closing Date'         = CONVERT(char(10),lne.closedate,101),
   'Maturity Date'        = CONVERT(char(10),lne.maturitydate,101),
   'Balloon Payment Date' = CONVERT(char(10),lne.balldate ,101),
   'Company Name 2'       = cmp2.cmpy_name
FROM loan lne
   INNER JOIN company cmp1 ON cmp1.cmpy_id = lne.ccmpy_id
   INNER JOIN company cmp2 ON cmp2.scmpy_id = lne.scmpy_id
WHERE maturitydate < GetDate() AND retired <>'Y'
ORDER BY maturitydate

Plus, I have an irrational dislike for correlated subqueries (although at times they are useful).

I've also used a different syntax for naming the columns which I think helps with clarity and ease of scanning.

-------------------------------------
A sacrifice is harder when no one knows you've made it.