inner join query problem

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

i have three tables 1. member_commission 2. member_payment 3. member_detail
now i want to join all three tables to get common deatil..my query is..

SQL

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 ...

Posted 16-Nov-12 21:11pm

Miss Maheshwari

Updated 16-Nov-12 23:40pm

v8

Add a Solution

Comments

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 www.dharmiksamajiksamuh.com/member/register.aspx?referal=n&id=103 NULL
a a 104 aa asa a A aaa a 2112012104QA 2 22223 102 www.dharmiksamajiksamuh.com/member/register.aspx?referal=aa&id=MTA0 NULL
x x 105 xx s x x x x 2112012105ZQ 2 545646 103 www.dharmiksamajiksamuh.com/member/register.aspx?referal=xx&id=KBPRcdIqXis= NULL
123 ayaan 106 aa Indore nmbdj jsd bkjs bkjs nash 011201273813 0 198293 102 www.dharmiksamajiksamuh.com/register.aspx?referal=aa&id=VGBkEUetTKs= NULL

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)

ankur789 · Answer 1 · 2012-11-16T22:33:00

Solution 1

give more detail about tables

Posted 16-Nov-12 22:33pm

ankur789

Comments

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.