SUM and LEFT JOIN

[igloo2001]

Hi the list !

I have a table PEOPLE which contains names.
I also have three other tables which contain names of PEOPLE with quantities
and a price.

I will like to carry out a sum of QTE*PRICE per name on the whole of
the three tables the whole in only one line per name:

example for the data:

PAUL 300 27 36 (result to obtain)

But my select does not seem to function, I obtains

PAUL 50 27 36

it does not make a nap if in a table there is
more than two lines (even with a GROUP BY name)
Thanks a lot for your assistance.

HERE DUMP OF MY BASE AND MY SELECT

DUMP

CREATE TABLE people (
name varchar(100) NOT NULL default ''
) TYPE=MyISAM;

INSERT INTO people VALUES ('PAUL');

--------------------------------------------------------

CREATE TABLE table1 (
name varchar(100) NOT NULL default '',
qte int(11) NOT NULL default '0',
price int(11) NOT NULL default '0'
) TYPE=MyISAM;

INSERT INTO table1 VALUES ('PAUL',10,5);
INSERT INTO table1 VALUES ('PAUL',5,50);

--------------------------------------------------------

CREATE TABLE table2 (
name varchar(100) NOT NULL default '',
qte int(11) NOT NULL default '0',
price int(11) NOT NULL default '0'
) TYPE=MyISAM;

INSERT INTO table2 VALUES ('PAUL',3,9);

--------------------------------------------------------

CREATE TABLE table3 (
name varchar(100) NOT NULL default '',
qte int(11) NOT NULL default '0',
price int(11) NOT NULL default '0'
) TYPE=MyISAM;

INSERT INTO table3 VALUES ('PAUL',12,3);

SELECT

SELECT people.name,

IFNULL((table1.qtetable1.price),0) AS total_table1,
IFNULL((table2.qtetable2.price),0) AS total_table2,
IFNULL((table3.qte*table3.price),0) AS total_table3

FROM people

LEFT JOIN table1 ON table1.name = people.name
LEFT JOIN table2 ON table2.name = people.name
LEFT JOIN table3 ON table3.name = people.name

GROUP BY name

[Anon]

Another example of where the crappy implementation of group by in Mysql leads you astray.

The query you wrote would not be allowed in any other RDBMS since you do not have any set functions on the expressions not in the group by clause.

Even if you have a sum, such as

SELECT people.name,
SUM(IFNULL((table1.qtetable1.price),0)) AS total_table1,
SUM(IFNULL((table2.qtetable2.price),0)) AS total_table2,
SUM(IFNULL((table3.qte*table3.price),0)) AS total_table3
FROM people
LEFT JOIN table1 ON table1.name = people.name
LEFT JOIN table2 ON table2.name = people.name
LEFT JOIN table3 ON table3.name = people.name
GROUP BY name

would not give you the intended result. The result of this query is

PAUL 300 54 72

You should not join these tables as table1, table2 and table3 are unrelated. The query you are looking for is

select name,
(select sum(nullif(qteprice,0))
from table1 where name = p.name ) as total_table1,
(select sum(nullif(qteprice,0))
from table2 where name = p.name ) as total_table2,
(select sum(nullif(qte*price,0))
from table3 where name = p.name ) as total_table3
from people p

As this type of query is not supported in Mysql (surprise!) you are forced, as far as I can see anyhow, to make three different queries

select sum(nullif(qte*price,0)) as total_table1

from people left join table1 on people.name = table1.name

et.c.

[Anon]

Thaks a lot. If anyone have an idea too...