JOIN with 3 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

JOIN with 3 tables

Author

Topic

nilaavu
Starting Member

18 Posts

Posted - 2013-05-07 : 14:44:40

Table A ( Year , period , A_Amount)
Table B ( Year , period , B_Amount)
Table C ( Year , period , C_Amount)

A INNERJOIN B on A.year =B.year and A.period =B.period
INNER JOIN C
on C.year =A.year and C.period =A.period
Above query doesnt work

How can I get all records from 3 tables by joing them ?

chadmat
The Chadinator

1974 Posts

Posted - 2013-05-07 : 16:14:14

Add a SELECT cause and a space between INNER and JOIN

-Chad

nilaavu
Starting Member

18 Posts

Posted - 2013-05-07 : 16:39:34

Yes I didnt miss the select in my query I was just showing how I created the JOIN . It did not pull all records from all tables.

chadmat
The Chadinator

1974 Posts

Posted - 2013-05-07 : 17:02:25

Give sample data and expected result.

SELECT * FROM
A INNER JOIN B on A.year =B.year and A.period =B.period
INNER JOIN C
on C.year = A.year and C.period = A.period

Will return rows where years and periods are equal across all 3 tables.

-Chad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-08 : 00:44:47

[code]
SELECT Year,Period,
SUM(A_Amount) AS A_Amount,
SUM(B_Amount) AS B_Amount,
SUM(C_Amount) AS C_Amount
FROM
(
SELECT Year , period , A_Amount,CAST(0.00 AS Numeric(10,2)) AS B_Amount,CAST(0.00 AS Numeric(10,2)) AS C_Amount
FROM TableA

UNION ALL

SELECT Year , period , 0.00,B_Amount,0.00
FROM TableB

UNION ALL

SELECT Year , period , 0.00,0.00,C_Amount
FROM TableC
)t
GROUP BY Year,Period
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

chadmat
The Chadinator

1974 Posts

Posted - 2013-05-08 : 12:18:55

quote:
Originally posted by visakh16

SELECT Year,Period,
SUM(A_Amount) AS A_Amount,
SUM(B_Amount) AS B_Amount,
SUM(C_Amount) AS C_Amount
FROM
(
SELECT Year , period , A_Amount,CAST(0.00 AS Numeric(10,2)) AS B_Amount,CAST(0.00 AS Numeric(10,2)) AS C_Amount
FROM TableA

UNION ALL

SELECT Year , period , 0.00,B_Amount,0.00
FROM TableB

UNION ALL

SELECT Year , period , 0.00,0.00,C_Amount
FROM TableC
)t
GROUP BY Year,Period

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

How did you arrive at this answer based on the info given? Did I miss something?

-Chad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-08 : 12:35:32

quote:
Originally posted by chadmat

quote:
Originally posted by visakh16

SELECT Year,Period,
SUM(A_Amount) AS A_Amount,
SUM(B_Amount) AS B_Amount,
SUM(C_Amount) AS C_Amount
FROM
(
SELECT Year , period , A_Amount,CAST(0.00 AS Numeric(10,2)) AS B_Amount,CAST(0.00 AS Numeric(10,2)) AS C_Amount
FROM TableA

UNION ALL

SELECT Year , period , 0.00,B_Amount,0.00
FROM TableB

UNION ALL

SELECT Year , period , 0.00,0.00,C_Amount
FROM TableC
)t
GROUP BY Year,Period

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

How did you arrive at this answer based on the info given? Did I miss something?

-Chad

Thats called experience

Its just a typical scenario where you want to aggregate fields across different tables based on some common field group. Whilst you can do this using joins, you will need FULL JOIN to make sure cases which are not present in all the tables are also getting included. Thats why i use UNION ALL approach which I've found to be much optimized compared to JOIN equivalent method at least more than once.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

chadmat
The Chadinator

1974 Posts

Posted - 2013-05-08 : 13:05:19

quote:
Originally posted by visakh16

Thats called experience

I guess so. I didn't see anything about aggregating anything in the question.

-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources