I have a table named registration having policynumber,name column and 4 different amount related tables with column as policynumber, amount common to all
Registration
PolicyNumber Name
1 A
2 B
3 C
4 D
Table A
policynumber Amount
1 200
2 500
4 1900
Table B
policynumber Amount
4 900
Here is the query
(only related to registration and table A)
Select sr.policynumber,cl.amount from registration sr LEFT JOIN tableA cl ON sr.policynumber = cl.policynumber and month = 'December' and year = 2012
this query gets me the data
sr.Policynumber cl.amount
1 200
2 500
3 NULL
4 1900
Similarly i created a query relating to registration and table b, which gives me
Select sr.policynumber,tableB.amount from registration sr LEFT JOIN tableB sl ON sr.policynumber = sl.policynumber and month = 'December' and year = 2012
Policynumber TableB.amount
1 NULL
2 NULL
3 NULL
4 900
But, i need to create such a join, which will get me a single row with data from both the tables, like this
Policynumber TableA.amount TableB.amount
1 200 NULL
2 500 NULL
3 NULL NULL
4 1900 900
How to do this?
Registration
PolicyNumber Name
1 A
2 B
3 C
4 D
Table A
policynumber Amount
1 200
2 500
4 1900
Table B
policynumber Amount
4 900
Here is the query
(only related to registration and table A)
Select sr.policynumber,cl.amount from registration sr LEFT JOIN tableA cl ON sr.policynumber = cl.policynumber and month = 'December' and year = 2012
this query gets me the data
sr.Policynumber cl.amount
1 200
2 500
3 NULL
4 1900
Similarly i created a query relating to registration and table b, which gives me
Select sr.policynumber,tableB.amount from registration sr LEFT JOIN tableB sl ON sr.policynumber = sl.policynumber and month = 'December' and year = 2012
Policynumber TableB.amount
1 NULL
2 NULL
3 NULL
4 900
But, i need to create such a join, which will get me a single row with data from both the tables, like this
Policynumber TableA.amount TableB.amount
1 200 NULL
2 500 NULL
3 NULL NULL
4 1900 900
How to do this?