JOIN with 3 trables 1

[Injun]

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 ?

 

[TamarGranor]

Are you trying to dump the records from all three tables into a single table, but keep them as separate records? If so, use UNION:

SELECT * FROM A 
UNION ALL
SELECT * FROM B
UNION ALL 
SELECT * FROM C

If you're trying to combine data from the records, so that you can have one record for each period showing the three amounts, you need to use outer joins, so that you get results for those periods where you have no data in one or more of the tables:

SELECT COALESCE(A.Year, B.Year, C.Year) AS Year, 
       COALESCE(A.Period, B.Period, C.Period) AS Period, 
       A_Amount, B_Amount, C_Amount
  FROM A 
    FULL JOIN B
      ON A.year =B.year and A.period =B.period
    FULL JOIN C
      ON A.year = C.year and A.period = C.period

Tamar

 

[Injun]

Thanks ! The second query is exactly what I am looking for.Solved my issue!

 

[gbaughma]

(Just a reminder... little stars for helpful posts encourage more helpful posts) <smiles>



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com