Sql For Join 3 Tables | Newbie | | Join Date: Aug 2007
Posts: 3
| | |
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.
|  | Moderator | | Join Date: Nov 2006 Location: Iceland
Posts: 3,754
| | | re: Sql For Join 3 Tables
Hi, and welcome to TSDN!
What type of SQL server are you using?
Have you made any attempts at this yourself?
| | Familiar Sight | | Join Date: Feb 2007 Location: Bristol UK
Posts: 227
| | | re: Sql For Join 3 Tables
i think you need to read this
regards
jx2
| | Newbie | | Join Date: Aug 2007
Posts: 3
| | | re: Sql For Join 3 Tables
Quote:
Originally Posted by Atli 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
| | Moderator | | Join Date: Nov 2006 Location: Iceland
Posts: 3,754
| | | re: Sql For Join 3 Tables
Quote:
Originally Posted by zarqane 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
-
| | Newbie | | Join Date: Aug 2007
Posts: 3
| | | re: Sql For Join 3 Tables
Quote:
Originally Posted by Atli 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.
| | Moderator | | Join Date: Nov 2006 Location: Iceland
Posts: 3,754
| | | re: Sql For Join 3 Tables
Why are you using SUM() for the invoice?
Try removing the SUM() function and just return 'table 2.invoice'.
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,510 network members.
|
