SQL Syntax: LEFT OUTER JOIN (Group By Table)

Posted on 2009-04-13
Last Modified: 2012-05-06
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?
Question by:djlurch
  • 2
  • 2
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24133716
you might try this:
SELECT cp.clpr_eventid, SUM(cp.clpr_amount) AS total_payments , e.event_name
FROM clients_payments_received cp
  ON ( e.eventid = cp.clpr_eventid )
GROUP BY  cp.clpr_eventid, e.event_name

Open in new window


Author Comment

ID: 24133927
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.
LVL 142

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 24133968
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


Author Closing Comment

ID: 31569715
You rock. Thanks!

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

815 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now