SQL Query for "sum" value to be 0 if no records match WHERE!

[gangel]

Hi ppl,



Sorry for the vague subject, i dont know how to do a title for this one!!

Im using access to create SQL queries for a MySQL database.
I know i havent got this is in the MySQL section, but i think it is relevant as a general "query" question!

Anyway i have this query:

"SELECT customers.FirstName, scripts.ClaimID, customers.CustomerID,customers.ZCSignup,customers.ClaimPaperwork, customers.LastName, IFNULL(sum(scripts.PharmAmount),0) AS SumOfInvAmount FROM (customers INNER JOIN claims ON customers.CustomerID = claims.customerID) INNER JOIN scripts ON claims.ClaimID = scripts.ClaimID WHERE scripts.ReconcilID IS NULL AND scripts.ChemistID='".$_SESSION['chsh']."' GROUP BY customers.FirstName, customers.LastName ORDER BY customers.LastName ASC"

The idea is to generate a list of Customers, whose [SCRIPTS] are not reconciled. Thus WHERE scripts.reconcilID IS NULL.
I want this to display customer.lastname, customer.firstname and SUM(scripts.pharmamount) but if they have NO SCRIPTS that fit this WHERE clause, then the customer is still there but the sum =$0.00!!!

I have read some things about doing a SELECT within a SELECT and using IFNULL etc etc but it is beyond me and no matter how many i read, i cant get the syntax right!!!

Can anyone help or point me in the right direction??

Gangel

[ItsMe]

Are you using a pass through query where all of the SQL is run on the server or are you having Access run the query?


I do not have MySQL experience but it seems as simple as placing precedence on the claims and scripts tables. Then change the INNER JOIN to a LEFT JOIN on the other (where you join the Customers and Claims tables.

[gangel]

im actually running it as a normal query on my access front end but am trying to slowly move everything online to web based PHP Mysql.

Ill try what you said, but im sure ive tried that before and it didnt work. Still only showed the ones i with null values for reconcilID only

[ItsMe]

If Access is running the query, you will get an error when saving a query with IFNULL. At least, I do not believe that is a valid function in Access SQL. I would use NZ() instead. Also, if you are depending on the internet to connect to the server, processing queries on the desktop will cause undesirable results. If Access looses connection to the DB, you can disrupt Access' ability to maintain data integrity. Even a slow connection can disrupt integrity if the flow of statements rely on earlier processes being complete.