Problem w- JOIN yielding unexpected result 1

[admoore]

I have a database which stores 2 types of "points" for various users identified by both equipment serial #'s or "vins" as well as by account #'s

I am attempting to generate a report showing the SUM of two types of points available for each serial number.

Although the query below executes fine and the "reward" result is correct, the sales_reward sum of points_sales.points is always incorrect.

Any help resolving how I made a mistake crafting this query would be appreciated...

SELECT users_vins.vin, users_vins.reg_date,  Sum( points_service.points ) AS reward,
                  Sum( points_sales.points ) AS sales_reward 
                  FROM users_vins
                  INNER JOIN points_service ON users_vins.acct_no = points_service.acct_no
                        JOIN points_sales ON users_vins.acct_no = points_sales.acct_no
                  GROUP BY users_vins.acct_no, vin
                  ORDER BY users_vins.acct_no ASC

TIA,

-A

 

[admoore]

This didn't work, either... It errored upon execution

                 SELECT users_vins.acct_no, 
                            users_vins.vin, 
                       users_vins.reg_date,  
    Sum( points_service.points ) AS reward,
( SELECT Sum( points_sales.points ) where users_vins.acct_no = points_sales.acct_no ) AS sales_reward
                  FROM users_vins
                  INNER JOIN points_service ON users_vins.acct_no = points_service.acct_no 
                   GROUP BY users_vins.acct_no, vin
                  ORDER BY users_vins.acct_no ASC

 

[admoore]

Ahhh... This worked...

                 SELECT 
                            users_vins.vin,
                       users_vins.reg_date,  
    Sum( points_service.points ) AS reward,
( SELECT Sum( points ) FROM points_sales where users_vins.acct_no = points_sales.acct_no ) AS sales_reward
                  FROM users_vins
                  INNER JOIN points_service ON users_vins.acct_no = points_service.acct_no
                   GROUP BY users_vins.acct_no, vin
                  ORDER BY users_vins.acct_no ASC

 

[r937]

what about this --

SELECT users_vins.vin
     , users_vins.reg_date
     , sv.service_reward
     , sl.sales_reward                  
  FROM users_vins 
INNER 
  JOIN ( SELECT acct_no
              , SUM(points) AS service_reward
           FROM points_service 
         GROUP
             BY acct_no ) AS sv
    ON sv.acct_no = users_vins.acct_no 
INNER 
  JOIN ( SELECT acct_no
              , SUM(points) AS sales_reward
           FROM points_sales 
         GROUP
             BY acct_no ) AS sl
    ON sl.acct_no = users_vins.acct_no

this isolates the two grouping operations into subqueries

your query was doing only one of the groupings in a subquery (in the SELECT clause) and the other in the outer query's GROUP BY

using subqueries for both groupings is potentially more efficient, as well as being obviously easier to maintain

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[admoore]

That seems much more elegant; however, the proposed query only returns a result where points_sales is not null. My original query returned correct results even when points_sales was null. As I require a return even if points_sales is null, can you suggest a variation?

TIA,

-A

 

[admoore]

Oops, sales_reward, not points_sales...

Sorry...

-A

 

[r937]

use LEFT OUTER JOINs instead of INNER JOINs



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[admoore]

Now that IS PERFECT! Tnx!