Forums Register Login

mysql join query

+Pie Number of slices to send: Send
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?
+Pie Number of slices to send: Send
You can join multiple tables in one query. Just add the second joins clause after the first one.
You guys wanna see my fabulous new place? Or do you wanna look at this tiny ad?
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 1123 times.
Similar Threads
Query performance
Hibernate - Object Graph using outer join
Inner join as a sub Query
Report Query
resultset is not working properly
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 03:29:53.