June 22, 2009 at 7:26 pm
Table: Trans
TransId TransDate Status user
T1 06/11/2009 Pending exec
T1 06/11/2009 Approved off
T2 06/13/2009 Pending exec
T2 06/14/2009 Deviated off
T2 o6/15/2009 Pending exec
T2 o6/16/2009 Approved off
Table: Payment
TransId PayId PayDate PaidAmt
T1 1 06/12/2009 300
T1 2 06/13/2009 4080
T2 3 06/14/2009 300
T2 4 06/15/2009 4080
query to display following Output
TransId TransDate Status user PayId PayDate PaidAmt
T1 06/11/2009 Pending exec 1 06/12/2009 300
T1 06/11/2009 Approved off 2 06/13/2009 4080
T2 06/13/2009 Pending exec 3 06/14/2009 300
T2 06/14/2009 Deviated off 3 06/14/2009 300
T2 o6/15/2009 Pending exec 3 06/14/2009 300
T2 o6/16/2009 Approved off 4 06/15/2009 4080
June 22, 2009 at 7:42 pm
Dont you have a unique transaction ID on trans table? Joining these tables will produce double the number of records.
--============
create table trans
(
transid varchar(10),
transdate datetime,
status varchar(20),
userID varchar(20)
)
create table Payment
(
TransID varchar(10),
Payid int identity,
Paydate datetime,
PaidAmt int
)
set dateformat 'mdy'
Insert into trans
select 'T1', convert(datetime,'06/11/2009'), 'Pending', 'exec'
Union
select 'T1 ', convert(datetime,'06/11/2009'), 'Approved', 'off'
Union
select 'T2 ', convert(datetime,'06/13/2009'), 'Pending', 'exec'
Union
select 'T2 ', convert(datetime,'06/14/2009'), 'Deviated', 'off'
Union
select 'T2 ', convert(datetime,'06/15/2009'), 'Pending ', 'exec'
Union
select 'T2 ', convert(datetime,'06/16/2009'), 'Approved ', 'off'
insert into payment(transid, paydate, paidamt)
select 'T1', '06/12/2009', 300
union
select 'T1', '06/13/2009', 4080
union
select 'T2', '06/14/2009', 300
union
select 'T2', '06/15/2009', 4080
select t.Transid, t.transdate, t.status, t.userid, p.payid, p.paydate, p.paidamt
from trans t
inner join payment p on t.transid=p.transid
--==============
OUTPUT
-------
Transid transdate status userid payid paydate paidamt
---------- ----------------------- -------------------- -------------------- ----------- ----------------------- -----------
T1 2009-06-11 00:00:00.000 Approved off 1 2009-06-12 00:00:00.000 300
T1 2009-06-11 00:00:00.000 Pending exec 1 2009-06-12 00:00:00.000 300
T1 2009-06-11 00:00:00.000 Approved off 2 2009-06-13 00:00:00.000 4080
T1 2009-06-11 00:00:00.000 Pending exec 2 2009-06-13 00:00:00.000 4080
T2 2009-06-13 00:00:00.000 Pending exec 3 2009-06-14 00:00:00.000 300
T2 2009-06-14 00:00:00.000 Deviated off 3 2009-06-14 00:00:00.000 300
T2 2009-06-15 00:00:00.000 Pending exec 3 2009-06-14 00:00:00.000 300
T2 2009-06-16 00:00:00.000 Approved off 3 2009-06-14 00:00:00.000 300
T2 2009-06-13 00:00:00.000 Pending exec 4 2009-06-15 00:00:00.000 4080
T2 2009-06-14 00:00:00.000 Deviated off 4 2009-06-15 00:00:00.000 4080
T2 2009-06-15 00:00:00.000 Pending exec 4 2009-06-15 00:00:00.000 4080
T2 2009-06-16 00:00:00.000 Approved off 4 2009-06-15 00:00:00.000 4080
(12 row(s) affected)
June 22, 2009 at 8:40 pm
Hi,
It should display 6 records only
June 22, 2009 at 9:12 pm
satishthota (6/22/2009)
Hi,It should display 6 records only
You have got 2 records with T1 in transID column in trans table and 2 records in payment table. so joining the table on this column will produce 2X2=4 records instead of 2 records for transID=2 as u desire.
I cant seem to find any other way to join these tables.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply