Hi,
I'm using this query, It works fine, except that i want to show PayType. When i tried to show it, I'll get duplicate records
What can i do?
Code Snippet
SELECT tbl_TransPayLnk.TransPayID, tbl_TransPayLnk.TransDate, tbl_TransPayLnk.Operator, tbl_TransPayLnk.Flagged, tbl_TransPayLnk.Remarks,
tbl_TransPayLnk.RemarksDate, tbl_Transaction.RefNo, tbl_Transaction.TransAmount, tbl_TransPayLnk.TransNo AS Expr1, tbl_TransPayLnk.PayID,
tbl_FundCode.FundDescription
FROM tbl_TransPayLnk LEFTOUTERJOIN
tbl_FundCode INNERJOIN
tbl_Transaction ON tbl_FundCode.FundCodeID = tbl_Transaction.FundCodeID ON tbl_TransPayLnk.TransNo = tbl_Transaction.TransNo
WHEREEXISTS
(SELECT PaymentID, PayID, PayType, Amount
FROM tbl_Payment AS tbl_Payment_1
WHERE(PayID = tbl_TransPayLnk.PayID))
Thanks
Prince:
I assume by your post that to add "PayType" that you need to also join to the tbl_Payment table. I would normally expect that the amounts would vary from record to record in that table. Also, I question the use of showing the "payType" without also showing the amount. If all you truely want is the "PayType" then changing your "SELECT" to a "SELECT DISTINCT" might be in order. Also, experiment with adding the AMOUNT and possibly the "PaymentID" to the data returned and see if the data makes more "sense" that way.
|||Kent
Prince
Try this:
Code Snippet
SELECT tbl_TransPayLnk.TransPayID, tbl_TransPayLnk.TransDate, tbl_TransPayLnk.Operator,
tbl_TransPayLnk.Flagged, tbl_TransPayLnk.Remarks,
tbl_TransPayLnk.RemarksDate, tbl_Transaction.RefNo, tbl_Transaction.TransAmount,
tbl_TransPayLnk.TransNo AS Expr1, tbl_TransPayLnk.PayID,
tbl_FundCode.FundDescription,
tbl_Payment_1.PayType
FROM tbl_TransPayLnk
LEFTOUTERJOIN tbl_FundCode
INNERJOIN tbl_Transaction
ON tbl_TransPayLnk.TransNo = tbl_Transaction.TransNo
INNERJOIN
(
SELECTDISTINCT PayID, PayType
FROM tbl_Payment
)AS tbl_Payment_1
ON tbl_TransPayLnk.PayID = tbl_Payment_1.PayID
P.S. Some sample DDL and sample data would sure make it easier to assist you....
|||Hi,
Right i tried your solution and got an error :
Code Snippet
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near 'PayID'.
As requested here are table structure and data:
Tbl_Transaction
Code Snippet
TransactionID int UncheckedTransNo varchar(50) Checked
RefNo nvarchar(50) Checked
FundCodeID smallint Checked
TransAmount decimal(18, 2) Checked
Description varchar(100) Checked
Data in Tbl_Transaction
Code Snippet
Tbl_TransPayLnk
Code Snippet
TransPayID int UncheckedPayID int Checked
TransNo varchar(50) Checked
TransDate datetime Checked
Operator char(5) Checked
TerminalID tinyint Checked
Flagged char(1) Unchecked
Remarks text Checked
RemarksDate datetime Checked
TPayment decimal(18, 2) Checked
Data Tbl_TransPayLnk
Code Snippet
Tbl_Payment
Code Snippet
PaymentID int UncheckedPayID int Checked
PayType varchar(50) Checked
Amount decimal(18, 2) Checked
Data Tbl_Payment
Code Snippet
And this is what i would need in one ROW
the only thing i want more in there is PAY TYPE without duplicating the rows. I mean as it is.
If i would add Payment tbl regardless of the joins i would be duplicating ROW 5 2 more times. so .......
Which would be cheque or cash.
Any ideas.
Thanks
|||Which row out of tbl_Payment do you want to be in the result?
How do you determine that that row should be it?
Do you just want one, you don't care which?
Or the highest amount?
|||Hi,
Well
If you look at this i'm getting PayID which is one. Against one in tbl_payment there is PayType. I just want PayType so cheque or cash would be against record PayID = 1
Thanks
|||Prince:
What is the complete key to the tbl_payment table? Also, doesn't the fact that the LEFT JOIN table participate in a subsequent inner join:
Code Snippet
INNERJOIN
tbl_Transaction ON tbl_FundCode.FundCodeID = tbl_Transaction.FundCodeID ON tbl_TransPayLnk.TransNo = tbl_Transaction.TransNo
turn the LEFT JOIN into a defacto INNER JOIN?
|||Hello Kent,
The primary key for tbl_Payment is PaymentID but that is not how i join. I joined it to tbl_transPayLnk using PayID which is copied in both these tables.
I don't know but what else can i do?
Read my long post and you get the idea of what i want?
Thanks
|||Prince
I commented out the reference to the FundCode table since you didn't provide the data.
Just uncomment it and add your ON clause to include that data.
Code Snippet
createtable tbl_Transaction (
TransactionID int,
TransNo varchar(50),
RefNo nvarchar(50),
FundCodeID smallint,
TransAmount decimal(18, 2),
Description varchar(100)
)
createtable tbl_TransPayLnk (
TransPayID int,
PayID int,
TransNo varchar(50),
TransDate datetime,
Operator char(5),
TerminalID tinyint,
Flagged char(1),
Remarks text,
RemarksDate datetime,
TPayment decimal(18, 2)
)
createtable tbl_Payment (
PaymentID int,
PayID int,
PayType varchar(50),
Amount decimal(18, 2)
)
setdateformat dmy
insertinto tbl_Transaction values(13,'1001','20013808', 1, 28.55,null)
insertinto tbl_Transaction values(14,'1001','34983249', 2, 9.31,null)
insertinto tbl_TransPayLnk values(5, 1,'1001','09/05/2007','02', 2,'V','hi there','13/06/2007', 37.86)
insertinto tbl_TransPayLnk values(6, 1003,'1002','09/05/2007','02', 2,'N',null,null, 73.33)
insertinto tbl_Payment values(17, 1,'Cheque', 30)
insertinto tbl_Payment values(18, 1,'Cash', 7.86)
insertinto tbl_Payment values(19, 1003,'Cheque', 73.33)
SELECT tbl_TransPayLnk.TransPayID, tbl_TransPayLnk.TransDate, tbl_TransPayLnk.Operator,
tbl_TransPayLnk.Flagged, tbl_TransPayLnk.Remarks,
tbl_TransPayLnk.RemarksDate, tbl_Transaction.RefNo, tbl_Transaction.TransAmount,
tbl_TransPayLnk.TransNo AS Expr1, tbl_TransPayLnk.PayID,
--tbl_FundCode.FundDescription,
tbl_Payment_1.PayType
FROM tbl_TransPayLnk
--LEFT OUTER JOIN tbl_FundCode
INNERJOIN tbl_Transaction
ON tbl_TransPayLnk.TransNo = tbl_Transaction.TransNo
INNERJOIN
(
SELECTDISTINCT PayID, PayType
FROM tbl_Payment
)AS tbl_Payment_1
ON tbl_TransPayLnk.PayID = tbl_Payment_1.PayID
and tbl_Payment_1.PayType =
(selecttop 1 paytype from tbl_Payment
where tbl_TransPayLnk.PayID = tbl_Payment_1.PayID
)
|||Hello Dale,
Thank you very much. I checked the code and although it does bring up the records. It only brought back Cheque even for the records that have cash in tbl_payment.
If it works fine, it should have brought cash and cheque for PayID= 1 which it didn't
Any ideas why?
Thanks I really appreciate your help.
|||OK; that helps. Now, are you expecting two lines to be displayed when there are two different pay types or are you wanting something like, 'Cheque, Cash' where the results are strung together? (Please forgive me, Dale)|||That's where I'm confused on what you're looking for.
How exactly do you want PayType to appear in the result?
|||There are only 2 pay types so it should appear as it is:
so for PayID =1 both cheque and cash should be displayed but without repeating the record 4 times.
Because rightnow it would do this if you omitt your last query. 2 cash and 2 for cheque for PayID=1
|||"... Now, are you expecting two lines to be displayed when there are two different pay types or are you wanting something like, 'Cheque, Cash' where the results are strung together? ..."
(Like I said: Please forgive me, Dale... *sigh* )
|||
Code Snippet
createfunction dbo.GetPayType ( @.PayID asint)
returnsvarchar(200)
as
begin
declare @.pt varchar(200)
select @.pt =coalesce( @.pt +',','')+ paytype
from tbl_Payment where payid = @.PayID
return @.pt
end
SELECT tbl_TransPayLnk.TransPayID, tbl_TransPayLnk.TransDate, tbl_TransPayLnk.Operator,
tbl_TransPayLnk.Flagged, tbl_TransPayLnk.Remarks,
tbl_TransPayLnk.RemarksDate, tbl_Transaction.RefNo, tbl_Transaction.TransAmount,
tbl_TransPayLnk.TransNo AS Expr1, tbl_TransPayLnk.PayID,
--tbl_FundCode.FundDescription,
dbo.GetPayType(tbl_TransPayLnk.PayID)as PayType
FROM tbl_TransPayLnk
--LEFT OUTER JOIN tbl_FundCode
INNER JOIN tbl_Transaction
ON tbl_TransPayLnk.TransNo = tbl_Transaction.TransNo
