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

SQL Syntax: LEFT OUTER JOIN (Group By Table)

Given: an events table and a clients_payments_received_table, using Access 2003

I would like to Left (outer) JOIN events to the following result

SELECT clients_payments_received.clpr_eventid, SUM(clients_payments_received.clpr_amount) AS total_payments
FROM clients_payments_received
GROUP BY  clients_payments_received.clpr_eventid

Would like to do this in one SQL statement.

Any ideas?
0
djlurch
Asked:
djlurch
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you might try this:
SELECT cp.clpr_eventid, SUM(cp.clpr_amount) AS total_payments , e.event_name
FROM clients_payments_received cp
LEFT OUTER JOIN events e
  ON ( e.eventid = cp.clpr_eventid )
GROUP BY  cp.clpr_eventid, e.event_name

Open in new window

0
 
djlurchAuthor Commented:
Uggg. Not what I was looking for. (My Fault) The query works...but it doesn't return the results that I want.

I am looking for all results from the events table with a ZERO value for events that don't have any payments.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no problem, just reverse the join:
SELECT e.eventid, e.event_name, SUM(cp.clpr_amount) AS total_payments 
FROM events e
LEFT OUTER JOIN clients_payments_received cp 
  ON ( e.eventid = cp.clpr_eventid )
GROUP BY e.eventid, e.event_name

Open in new window

0
 
djlurchAuthor Commented:
You rock. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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