3 table in query

putraaridana

please help

TABEL1
CODE NAME
001 MR. A
002 MR. B

TABEL2
CODE CODE1 QTY
001 X1 10
001 X2 15
002 X1 20

TABEL3
CODE CODE1 PAY
001 X1 $1
001 X2 $5
002 X1 $10

RESULT
CODE QTY PAY
001 15 $6
002 20 $10

I COULDN'T FIND THE WAY TO PUT THAT CASE IN THE QUERY
ANY IDEA ?

LordShryku

1) Loose the caps. All caps looks like yelling.
2) What are trying to do? What exactly is "That case"?

putraaridana

ok here's the case

i run this query

select a.code, a.name, sum(b.qty), sum(c.pay) from table1 as a, table2 as b left join table3 as c on a.code=b.code group by a.code

the result of sum become triple from it supose to be, is that because there's three record in table 3 ?

LordShryku

Well.....you never join table3....

putraaridana

so we can't join 3 table in one query ?
what other option that i can use ?

LordShryku

No no no, you CAN, but in the query you just posted, you have no join statement linking table3 to anything. Or now that I look at it again....table one, or no, table three...

from        table1 as a, 
               table2 as b 
  left join table3 as c 
        on a.code=b.code

Okay, so your join statment links table3 to table2, but your on statement links table1 to table2....

Try this:

SELECT a.code, 
       a.name, 
       sum(b.qty), 
       sum(c.pay) 
FROM table1 a
LEFT JOIN table2 b, table3 c
ON a.code=b.code 
AND a.code=c.code 
GROUP BY a.code

putraaridana

thank's for the correction and
soryy about the query, i already give it a try but seems the result of sum noT correct because there's a code 003 in table1
but have no records in table 2 and table3 the sum result is the same as code 002

i wonder why ??
any idea lord ?

LordShryku

Gimme a lil time to recreate the tables. You're example doesn't show a 003 in there, but I'll see what I can get outta it...

Just a thought...maybe add and ifnull clause to it....

LordShryku

Here ya go

SELECT a.code, 
       a.name, 
       sum(b.qty),
       sum(c.pay)
FROM table1 a
LEFT JOIN table2 b 
   ON a.code=b.code 
LEFT JOIN table3 c 
   ON a.code=c.code
GROUP BY a.code

putraaridana

thank's,
i found the problem is if in table2 there's 2 record
the result of sum will be twice and if 3 record the sum result is triple, the record in table 3 is doesn't matter
hope you read this, thank's
i try two left join it's have the same result

BlueMeteor

sum(b.qty) ??

max(b.qty).....^_

putraaridana

thank's guys
i found easy way
i use temporary table to store first query (with left join) and the temporary table left join with table2