This week's book giveaways are in the Refactoring and Agile forums.
We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Agile forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

mysql join query

 
Kunal Lakhani
Ranch Hand
Posts: 622
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Rob Spoor
Sheriff
Pie
Posts: 20495
54
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can join multiple tables in one query. Just add the second joins clause after the first one.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic