Rookie Recruits Rookie Recruits - 1 year ago 68
SQL Question

sql join query not returning data, just blank

I am trying to pull data from 2 separate tables but only specific columns, then I do a join so that only one row per client is displaying with the total sum of their payments but the information is not displaying. Code is below. It is for a reports page so think about geting the sum of all payments. I know what I am looking for, I just think that maybe there is a bug in the query that I can't seem to catch. I could use an extra pair of eyes to point out the flaw if possible. Thanks

SELECT, pre.loanAmount, pre.custId,
SUM(pay.amount) AS amount,
DISTINCT( AS company,
DISTINCT(pay.loanId) AS loanId
FROM preQualForm pre INNER JOIN
payments pay
ON pre.custId=pay.custId

Answer Source

The DISTINCT Keyword applies to all the columns you SELECT, so if you need to also do aggregate functions like SUM, then it is better achieved using a GROUP BY clause on non-aggregate columns.

The following should work.

    , pre.loanAmount
    , pre.custId
    , SUM(pay.amount) AS Amount
    , AS Company
    , pay.loanId AS LoanId
FROM preQualForm pre 
     INNER JOIN payments pay
     ON pre.custId = pay.custId
    , pre.loanAmount
    , pre.custId
    , pay.loanId

See Also: GROUP BY - Simple GROUP BY Example.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download