CODEWITHSUNDEEP
mysqlsql
alexcctan
alexcctan

Reputation: 123

sql subquery how to get the desired value

DECLARE @deposit TABLE(
  checks VARCHAR(MAX),
  deposit VARCHAR(MAX)
)

INSERT INTO @deposit
SELECT CheckID
     , Total
FROM
  DISCOUNT_DETAIL
  INNER JOIN CHECK_DETAIL
    ON DISCOUNT_DETAIL.CheckDetailID = CHECK_DETAIL.CheckDetailID
WHERE
  DISCOUNT_DETAIL.DscntID = '98'

temptable

SELECT CASE WHEN CHECK_DETAIL.CheckID IN (SELECT checks
                                          FROM @deposit) 
THEN CHECKS.Payment + (SELECT abs(deposit) FROM @deposit)
                  ELSE
  CHECKS.Payment
FROM ....

I received an error : subquery return more than one row.

How do I achieve if my check id match with the temp table check id then only add that deposit to payment.

I am using SQL server 2005.

Thanks

Upvotes: 0

Views: 36

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I think you want to join the tables together:

select c.payment + coalesce(abs(d.deposit), 0)
from checks c left outer join
     @deposit d
     on c.CheckId = d.checks
     . . .

This will add the absolute value of the deposit amount to each check. If there is no corresponding record in @deposit then the payment doesn't change.

Upvotes: 2

StanislavL
StanislavL

Reputation: 57381

THEN CHECKS.Payment + (SELECT abs(deposit) FROM @deposit)

This one returns multiple rows so it's not possible to add multiple sums to your payment

You can either use aggregation 

SELECT sum(abs(deposit)) FROM @deposit

or add some condition

SELECT abs(deposit) FROM @deposit where user_id=12345

Upvotes: 0

Related Questions