Click here to Skip to main content
15,112,662 members
Please Sign up or sign in to vote.
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
Updated 10-Mar-17 2:20am
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

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.
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900