How to Get record From 3 table.?

Question

1.00/5 (2 votes)

See more:

i posting queston third time,
>here are 3 table
>payment table is computed table and its have only one product data
>rcvamt and restamt get from payment table
Collection:

id(PK)	clientid	company		product		total
1	2001		Company1	Product1	50000
2	2002		Company2	Product2	60000
3	2003		Company3	Product3	70000

PaymentData:
wid(PK)	wcid(FK)	clientid	product		rcvamt	restamt
1	1		2001		Product1	500	49500
2	1		2001		Product1	800	48700
3	2		2002		Product2	1500	58500

Payment
id(PK)	wid(FK)	clientid	product		rcvamt	restamt
1	1	2001		Product1	1300	48700
2	3	2002		Product2	1500	58500


i want to shaw report like
clientid	company		procudt		total	rcvamt	restamt
2001		Company1	Product1	50000	1300	48700
2002		Company2	Product2	60000	1500	58500
2003		Company3	Product3	70000

Posted 27-Feb-15 21:41pm

Karmesh_Madhavi

Updated 28-Feb-15 0:54am

User 11060979

v3

Add a Solution

Comments

John C Rayan 28-Feb-15 4:20am

Collection has 1 to many relationship with Payment. So you would have more than one entry from PaymentData in your report. How do you get only 49500 not 48700. Can you ellobarate? in the report I would expect the restamt for product1 to be the sum of 49500 + 48700 not just 49500. Can you check.

Karmesh_Madhavi 28-Feb-15 4:35am

ok my mistake, its 48700. in Shaw report, so how i get it.?

[no name] 28-Feb-15 6:58am

http://www.w3schools.com/sql/sql_join_left.asp[^]

and
http://www.w3schools.com/sql/sql_functions.asp[^]

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)

kshitij ambre · Answer 1 · 2015-02-28T01:48:00

Hello,

I didn't understood your question well,
but i think this might help

----Create Table Script

Create Table Collection
(
id int Primary key ,
clientid Varchar(6),
company Varchar(30),
product Varchar(30),
total money
)

Insert Into Collection Values ('1','2001','Company1','Product1','50000')
Insert Into Collection Values ('2','2002','Company2','Product2','60000')
Insert Into Collection Values ('3','2003','Company3','Product3','70000')

Create Table PaymentData
(
wid int Primary key ,
wcid int FOREIGN KEY REFERENCES Collection(id),
clientid Varchar(6),
product Varchar(30),
rcvamt money,
restamt money
)

Insert Into PaymentData Values('1','1','2001','Product1','500','49500')
Insert Into PaymentData Values('2','1','2001','Product1','800','48700')
Insert Into PaymentData Values('3','2','2002','Product2','1500','58500')

Create Table Payment
(
id int Primary key ,
wid int FOREIGN KEY REFERENCES PaymentData(wid),
clientid Varchar(6),
product Varchar(30),
rcvamt money ,
restamt money,
)

Insert Into Payment Values('1','1','2001','Product1','1300','48700')
Insert Into Payment Values('2','3','2002','Product2','1500','58500')

---After Creating tables your solution should be

Select A.clientid,A.company,A.product,A.total,B.rcvamt,B.restamt From Collection A
Left Join Payment B
On A.id = B.id