joining two tables using mysql

Question

0.00/5 (No votes)

See more:

Table branch

Branch_ID---Branch_Name---Branch_City
SB001------Malleshwaram---Bangalore
SB002------MG Road--------Bangalroe
SB003------MG Road--------Mysore
SB004------Jainagar-------Mysore

table loan
Account_no----Branch_Id----Balance
AE1185698----SB001---------102000
AE8552266----SB003---------40000
AE1003996----SB004---------15000
AE1100996----SB002---------100000

i want the output in the below form

min-------city------max------city
15000----mysore----10200----Blore

i tried this

select min(balance), branch.city, max(balance), branch(city) from loan.

Thanks in advance

Posted 3-Jun-15 1:24am

raj@hardWork

Updated 3-Jun-15 1:34am

ZurdoDev

v3

Add a Solution

Comments

raj@hardWork 3-Jun-15 7:26am

I'm unable to add the tag MySQL

ZurdoDev 3-Jun-15 7:34am

You'll need 2 derived tables then. One that returns the min and min City and one that returns the max and max city.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2015-06-03T01:57:00

Solution 1

This would work ...

SQL

SELECT MINCTE.Balance, MINCTE.Branch_City, MAXCTE.Balance, MAXCTE.Branch_City
FROM (select L.*, B.Branch_City from loan L
    INNER JOIN branch B on L.Branch_Id=B.Branch_ID
    order by Balance LIMIT 1) MINCTE
CROSS JOIN
    (select L.*, B.Branch_City from loan L
    INNER JOIN branch B on L.Branch_Id=B.Branch_ID
    order by Balance desc LIMIT 1) MAXCTE

MINCTE is the derived table to get the minimum value and the associated city name
MAXCTE does the same for the maximum value
Those two derived tables are then CROSS joined

Posted 3-Jun-15 1:57am

CHill60

Updated 3-Jun-15 1:58am

v2

Comments

raj@hardWork 3-Jun-15 7:59am

Thank you so much.

raj@hardWork 3-Jun-15 8:02am

i also did something like this

select b.branchcity,max(L.balance) as max , b.branchcity,min(L.balance) from branch b join loan l on l.branchid=b.branchid group by b.branchcity

and even this is working, but thanks to you so much for the swift response.

CHill60 3-Jun-15 10:39am

Your query will return the max with city and min with city but on different rows (3 - there is a misspelling of 'Bangalore' in your data). Your expected results implied that you needed Max, city of Max, Min, City of Min in the same row, which is what my solution does

raj@hardWork 8-Jun-15 4:17am

correct thank you