Sql For Join 3 Tables

[zarqane]

I have three tables :

table 1 : Id,Name Example : (1 record): 1 , TOTO
table 2 : id,invoice Example : (1 record): 1 ,10 000.00
table 3 : id,payment Example : (2 records): 1 , 5 000.00
1, 2 500.00

What is the sql that i can have one line with the result as folows:

id Name Invoice Payment
1 TOTO 10 000.00 7 500.00

Thanks.

[Atli]

Hi, and welcome to TSDN!

What type of SQL server are you using?
Have you made any attempts at this yourself?

[jx2]

i think you need to read this

regards
jx2

[zarqane]

Hi, and welcome to TSDN!

What type of SQL server are you using?
Have you made any attempts at this yourself?

Mysql php

SELECT 
  table 1.id,
  table 1.name,
  sum(table 2.invoice),
  sum(table 3 payment)
FROM 
  table 1,table 2,table 3 
WHERE  table 1.id= table 2.id 
and  table 1.id= table 3.id   
GROUP by  table 1.id
 

The result is always the double amount of INVOICE ( SUM amount =
20 000.00

[Atli]

Mysql php

SELECT 
  table 1.id,
  table 1.name,
  sum(table 2.invoice),
  sum(table 3 payment)
FROM 
  table 1,table 2,table 3 
WHERE  table 1.id= table 2.id 
and  table 1.id= table 3.id   
GROUP by  table 1.id
 

The result is always the double amount of INVOICE ( SUM amount =
20 000.00

This is because you are not defining which columns the tables are sharing, which results in duplicate rows.

I recommend you read the link jx2 posted above, and then try doing something like this:

SELECT 
  table1.id,
  table1.name,
  sum(table2.invoice),
  sum(table3 payment)
FROM 
  table1
INNER JOIN table2
  ON table2.id = table1.id
INNER JOIN table3
  ON table3.id = table1.id
GROUP by  table1.id
 

[zarqane]

This is because you are not defining which columns the tables are sharing, which results in duplicate rows.

I recommend you read the link jx2 posted above, and then try doing something like this:

SELECT 
  table1.id,
  table1.name,
  sum(table2.invoice),
  sum(table3 payment)
FROM 
  table1
INNER JOIN table2
  ON table2.id = table1.id
INNER JOIN table3
  ON table3.id = table1.id
GROUP by  table1.id
 

I have trying left join and inner join as you recommended but the result continue the same as before.

B.REGARDS.

[Atli]

Why are you using SUM() for the invoice?
Try removing the SUM() function and just return 'table 2.invoice'.