Need help to perform this action in sql.. see description inside.

[nexusbr]

Hello there,
here is the scenario.
i have 3 tables, like the following

[TABLE 1: PRODUCTS]
[CODPROD],[ NAME]
1                 , PROD1
2                 , PROD2
3                 , PROD3
[END OF TABLE 1]

[TABLE 2: SALES_X]
[CODPROD], [AMT]
1                 , 3
2                 , 1
3                 , 1
2                 , 5
2                 , 1
1                 , 4
[END OF TABLE 2]

[TABLE 3: SALES_Y]
[CODPROD], [AMT]
3                 , 3
2                 , 1
2                 , 1
2                 , 1
1                 , 30
[END OF TABLE 3]

What i'm trying to do and cannot figure out how to.

sql result:
[PRODUCTS.NAME],[SALES_X.AMT + SALES_Y.AMT]
PROD1                   , 37
PROD2                   , 10                              
PROD3                   , 4

I Have tried using SUM(), COUNT(), Joins, Unions, but still did not get how to do it right..

i'm trying with this, but its bugged

select a.name, SUM(b.amt) + SUM(c.amt) from PRODUCTS a
LEFT JOIN SALES_X      b
ON b.codprod= a.codprod
LEFT JOIN SALES_Y   c
on c.codprod= a.codprod
GROUP BY A.NAME

i did manage a way for it on the application side, but it would be great to have a server-side solution for it...

[Atli]

Hey.

Using a JOIN in that manner is problematic. The effect of adding two identical tables, who are essentially the same table split in two, can lead to data being repeated to compensate for the mismatch in row count between tables. - Basically, you can end up repeating rows from either table, making the count inaccurate.

I suggest, rather than trying to use a JOIN, that you try using Correlated Subqueries. That is; subqueries that reference tables from the outer query.

Basically, rather than JOIN the tables, you simply fetch the SUM from the sales tables using a subquery. You then use the "codprod" key from the PRODUCTS table to filter the result set used by the subqueries, by having the subqueries reference it's parent query.

Like:
[code=sql]SELECT
a.name,
( SELECT SUM(x.amt)
FROM sales_x AS x
WHERE x.codprod = a.codprod
) + (
SELECT SUM(y.amt)
FROM sales_y AS y
WHERE y.codprod = a.codprod
) AS 'total'
FROM products AS a[/code]
That should give you an accurate count.


But as I said above, the two sales tables are essentially just two parts of a larger table. Therefore, you could use a UNION to construct the complete table, and use a JOIN on that.
[code=sql]SELECT
a.name,
SUM(s.amt)
FROM products AS a
LEFT JOIN (
SELECT * FROM sales_x
UNION ALL
SELECT * FROM sales_y
) AS s
ON s.codprod = a.codprod
GROUP BY a.name[/code]
However this is likely to be far less efficient than the subquery version, as this requires MySQL to temporarily construct the "sales" table, and it uses a GROUP BY clause.

[nexusbr]

Wow, excellent!!!

i just had a little trouble with NULL values, but it's fixed with a CASE WHEN clause..

and i'm using the first solution.

thank you very much brother.