SQL Query join rows help

[_taz_]

Hi, I'm new here, and still a noob in sql.
This is my query

SELECT     Payments.ClientID, Payments.CreditorID, Clients.FirstName, Clients.LastName, Clients.SSN, Clients.Address1, Clients.Address2, Clients.City, 
                      Clients.State, Clients.ZIP, Clients.HomePhone, Clients.WorkPhone, Clients.OtherPhone, SUM(Payments.Amount) 
FROM         Payments INNER JOIN
                      Clients ON Payments.ClientID = Clients.ClientID
WHERE     (Payments.CreditorID = 1015 or Payments.CreditorID= 1014) 
GROUP BY Payments.CreditorID, Payments.ClientID, Clients.FirstName, Clients.LastName, Clients.SSN, Clients.Address1, Clients.Address2, Clients.City, 
                      Clients.State, Clients.ZIP, Clients.HomePhone, Clients.WorkPhone, Clients.OtherPhone

works great, but some clients have creditor 1014 and 1015. I would like those totaled and only show once (I really don't need the creditorid column. just in there trying to help myself see what was going on.)

[dickersonka]

first do the select on payments where creditor.id = 1014 or creditor.id = 1015 and group by clientid with the sum

then create the outer select doing the inner join on client id

[_taz_]

ok, well not sure I understand your solution. I just started using inner joins, don't know what an outer s for yet. I have redone it from reading here and there. and now I've got it putting the values for different creditors in different columns, but it creates a row for every creditor instead of putting the value in the existing row. I know I need to take a class or rtm, but trying to learn on the fly.

SELECT     p.ClientID, c.WholeName, 
                CASE WHEN p.creditorid = 1014 THEN SUM(p.amount) ELSE 0 END AS [1014], 
                CASE WHEN p.creditorid = 1015 THEN SUM(p.amount) ELSE 0 END AS 1015],      CASE WHEN p.creditorid = 8249 THEN SUM(p.amount) 
                      ELSE 0 END AS [8249], CASE WHEN p.creditorid = 8259 THEN SUM(p.amount) ELSE 0 END AS [8259], 
                      CASE WHEN p.creditorid = 8260 THEN SUM(p.amount) ELSE 0 END AS [8260], CASE WHEN p.creditorid = 8261 THEN SUM(p.amount) 
                      ELSE 0 END AS [8261], CASE WHEN p.creditorid = 8262 THEN SUM(p.amount) ELSE 0 END AS [8262], 
                      CASE WHEN p.creditorid = 313200 THEN SUM(p.amount) ELSE 0 END AS [313200]
FROM         Payments p LEFT JOIN
                      Clients c ON p.ClientID = c.ClientID LEFT JOIN
                      Creditors cr ON p.CreditorID = cr.CreditorID
WHERE     c.ClientStatus LIKE 'Active%' AND (cr.CreditorID = 1014 OR
                      cr.creditorid = 1015 OR
                      cr.creditorid = 8249 OR
                      cr.creditorid = 8259 OR
                      cr.creditorid = 8260 OR
                      cr.creditorid = 8261 OR
                      cr.creditorid = 8262 OR
                      cr.creditorid = 313200)
GROUP BY p.clientID, c.wholename, p.creditorid
ORDER BY p.clientID

can I make it put them all on one row?

[dickersonka]

i didn't test this, might be a syntax error, but this is what you need to do

select 
	dv.clientid, c.lastname 
	-- put the rest of your columns here
from 
(select clientid, sum(amount) from payments where creditorid = 1015 or creditorid = 1014 
group by clientid) as dv
inner join clients c on 
dv.clientid = c.clientid