Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP 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: 20608
63
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