How can I join 3 tables by using 2 different where clauses for each ?

Question

0.00/5 (No votes)

See more:

i have 3 tables in my database as account,deposite and withdraw. each table have account_no column in common.

now i need to display account_no from account table, amount column which is having >25000 from deposite table and amount column which is having <50000 from withdraw table.

What I have tried:

i have tried the below query

select a.acnt_no,d.deposited_amt,w.balance from acnt a ,deposite d,withdrawl w 
where ((a.acnt_no = d.acnt_no) and (a.acnt_no = w.acnt_no)) 
and (d.deposited_amt>25000 and w.balance>50000)

and i have tried with inner join and full outer join also. all 3 got the same answer that

ac.no amount amount
104 25400.00 66400.00
106 32500.00 75000.00
110 250000.00 78900.00

but i have some more records which are satisfying the condition in both the amounts columns from deposite and withdrawl tables.

i think the result is based on records that are only satisfying the both conditions

Posted 10-Mar-17 1:13am

Member 11148917

Updated 10-Mar-17 1:20am

Add a Solution

Comments

Member 11148917 10-Mar-17 8:38am

Thanks a lot for ur response and i got the same result as above that

104 25400.00 66400.00
106 32500.00 75000.00
110 250000.00 78900.00

first account table data is
acno cust_name balance
101 aa 200000.00
102 bb 235000.00
103 cc 450000.00
104 dd 350000.00
105 ee 985000.00
106 ff 567000.00
107 gg 262000.00
108 hh 655000.00
109 ii 785000.00
110 jj 489000.00

the deposite table data is

acno deposited_amt
101 10000.00
102 12500.00
103 15500.00
104 25400.00
105 26000.00
106 32500.00
107 15400.00
108 100000.00
109 200000.00
110 250000.00

withdraw table data is

acnt_no balance
101 15000.00
102 75000.00
103 85500.00
104 66400.00
105 12000.00
106 75000.00
107 45400.00
108 10000.00
109 45600.00
110 78900.00

and i want to get result table with 3 columns with acno_no,deposited_amt in deposite table which is >25000 and balance in withdraw table which is >50000

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)

OriginalGriff · Answer 1 · 2017-03-10T01:20:00

Try:

SQL

SELECT a.acnt_no, d.deposited_amt, w.balance FROM acnt a
JOIN deposite d ON a.acnt_no = d.acnt_no
JOIN withdrawl w ON a.acnt_no = w.acnt_no
WHERE d.deposited_amt > 25000 AND w.balance > 50000

If that doesn't work, you'll have to provide sample table data for all three tables, the output you expect from that data, and the output you get from the above query.