Corelated Sub query

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 Unchecked
TransNo 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

TransactionID TransNo RefNo FundCodeID TransAmount Description 13 1001 20013808 1 28.55 14 1001 34983249 2 9.31

Tbl_TransPayLnk

Code Snippet

TransPayID int Unchecked
PayID 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

TransPayID PayID TransNo TransDate Operator TerminalID Flagged Remarks RemarksDate TPayment 5 1 1001 09/05/2007 02 2 V hi there 13/06/2007 37.86 6 1003 1002 09/05/2007 02 2 N 73.33

Tbl_Payment

Code Snippet

PaymentID int Unchecked
PayID int Checked
PayType varchar(50) Checked
Amount decimal(18, 2) Checked

Data Tbl_Payment

Code Snippet

PaymentID PayID PayType Amount 17 1 Cheque 30 18 1 Cash 7.86 19 1003 Cheque 73.33

And this is what i would need in one ROW

TransPayID TransDate Operator Flagged Remarks RemarksDate RefNo TransAmount TransNo PayID FundDescription 5 09/05/2007 02 V hi there 13/06/2007 20013808 28.55 1001 1 Rent 5 09/05/2007 02 V hi there 13/06/2007 34983249 9.31 1001 1 Sundry Debtor 6 09/05/2007 02 N 20013808 73.33 1002 1003 TV Licence

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

TransPayID TransDate Operator Flagged Remarks RemarksDate RefNo TransAmount TransNo PayID FundDescription 5 09/05/2007 02 V hi there 13/06/2007 20013808 28.55 1001 1 Rent 5 09/05/2007 02 V hi there 13/06/2007 34983249 9.31 1001 1 Sundry Debtor 6 09/05/2007 02 N 20013808 73.33 1002 1003 TV Licence

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