File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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 Murach's Java Servlets and JSP this week in the Servlets 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: 622
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: 19651
    
  18

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
resultset is not working properly
Inner join as a sub Query
Query performance
Report Query