This week's book giveaway is in the Java 8 forum.
We're giving away four copies of Java 8 in Action and have Raoul-Gabriel Urma, Mario Fusco, and Alan Mycroft on-line!
See this thread for details.
The moose likes JDBC and the fly likes mysql join query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "mysql join query" Watch "mysql join query" New topic
Author

mysql join query

Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 618
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?


kunal
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19556
    
  16

You can join multiple tables in one query. Just add the second joins clause after the first one.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: mysql join query
 
Similar Threads
Hibernate - Object Graph using outer join
Query performance
Report Query
Inner join as a sub Query
resultset is not working properly