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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 143

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 143

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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