SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 JOIN with 3 tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nilaavu
Starting Member

18 Posts

Posted - 05/07/2013 :  14:44:40  Show Profile  Reply with Quote

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

USA
1974 Posts

Posted - 05/07/2013 :  16:14:14  Show Profile  Visit chadmat's Homepage  Reply with Quote
Add a SELECT cause and a space between INNER and JOIN

-Chad
Go to Top of Page

nilaavu
Starting Member

18 Posts

Posted - 05/07/2013 :  16:39:34  Show Profile  Reply with Quote
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.




Edited by - nilaavu on 05/07/2013 16:41:23
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 05/07/2013 :  17:02:25  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/08/2013 :  00:44:47  Show Profile  Reply with Quote

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
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 05/08/2013 :  12:18:55  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/08/2013 :  12:35:32  Show Profile  Reply with Quote
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
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 05/08/2013 :  13:05:19  Show Profile  Visit chadmat's Homepage  Reply with Quote
quote:
Originally posted by visakh16

Thats called experience




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

-Chad
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000