Click here to Skip to main content
15,798,592 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i have three tables 1. member_commission 2. member_payment 3. member_detail
now i want to join all three tables to get common query is..

select r.ref_id,sum(r.ref_com),m.mname,p.bal_amt from member_commission  as r inner join member_detail as m
on r.ref_id=m.mem_id inner join member_payment  as p on r.ref_id=p.mem_id where r.pay_check='unpaid' group by r.ref_id

to get total commission earn by all different members,their id and their name and finally last unpaid balance
the details are coming from all these three tables but my query is not working,
there are some error on mname

please give me some exactly idea how to fetch details from three tables ...
Updated 17-Nov-12 0:40am
vishal jodh 17-Nov-12 4:09am    
pls post table Structure of u r query
Miss Maheshwari 17-Nov-12 4:41am    
table member_commission
cid ref_type ref_id ref_com mid time pay_check
1 indirect 106 1 113 2012-11-16 22:32:38.000 NULL
2 indirect 103 2 113 2012-11-16 22:32:38.000 NULL
3 indirect 101 3 113 2012-11-16 22:32:38.000 NULL
4 direct 106 100 113 2012-11-16 22:32:38.000 NULL

table member_payment
pid ref_id payamt totamt balamt date
1 106 101 201 100 2012-11-16 00:00:00.000

table member_detail
name userid mid add and other field
a abc 102 a abshbjs asa 11 asa 12112 1112012102CL 1 111 101 NULL NULL
a neetu1 103 n n 545 gg 46 gjh ttut 21120121033F 2 4 102 NULL
a a 104 aa asa a A aaa a 2112012104QA 2 22223 102 NULL
x x 105 xx s x x x x 2112012105ZQ 2 545646 103 NULL
123 ayaan 106 aa Indore nmbdj jsd bkjs bkjs nash 011201273813 0 198293 102 NULL

1 solution

give more detail about tables
Share this answer
Miss Maheshwari 17-Nov-12 4:37am    
table member_detail is having complete detail of members..
table member_commission is having detail of commission earned by members
and table member_payment is having detail of commission paid to member by admin and balance of of member
Nelek 17-Nov-12 5:02am    
Please don't post solutions to add information, to ask something or to comment another user.
- To add information to your message, you can use the widget "Improve question" / "Improve solution" at the bottom of your text.
- To answer a user, you can use the widget "Have a question or comment?" (as I am doing right now with you) or the widget "reply" in another comment.

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