Problem w- aliases and multiple joins

[admoore]

I am having problems with a multiple join query...

While this query produces an erroneous result due to no aliases defining multiple joins, it does run:

           SELECT users_vins.vin, users_vins.reg_date,  Sum( points_service.points ) AS service_reward, Sum( points_sales.points ) AS sales_reward 
                  FROM users_vins
                  INNER JOIN points_service ON users_vins.acct_no = points_service.acct_no 
                  INNER 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

This query attempts to solve the erroneous result with aliases; but, produces an error: [COLOR=red yellow]#1109 - Unknown table 'points_service' in field list[/color]

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

Can anyone help with my syntax please?

TIA,

Allen M.

 

[DonQuichote]

While this query produces an erroneous result due to no aliases defining multiple joins ...

What do you mean? aliases are not necessary and overly use of them only make queries illegible.

But if you use them, you should use them consistently. Once you have given a table an alias, call it by the alias, also in its own JOIN clause:

INNER JOIN points_service AS service ON users_vins.acct_no = [highlight]service[/highlight].acct_no

The reason is that you can join to the same table twice, one time non-aliased and one time aliased.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[admoore]

After correcting my aliases like this:

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

I no longer have the error; but the result is still erroneous with sums far in excess of those that actually exist, presumably due to the multiple joins...