bring back records with no match

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

bring back records with no match

Author

Topic

aivoryuk
Starting Member

9 Posts

Posted - 2011-01-26 : 09:30:13

Hi I have a query that I need some help on.

I have a table which contains the following columns

accountid, transactionamount, merchant, effectivedate, transactiontype

In the transactiontype it indicates whether the record is a debit or credit ('d','c').

What I am trying to do is to bring back records of credits from a merchant where there is no corresponding debit to that merchant.

I've been using the following code but it is not bringing back what I want, I'm using a left outer join
left outer join DebitCardTransactionFinancial as d with (nolock)
on c.accountdetailid=d.accountdetailid
and c.merchant=d.merchant
and c.transactionamount=d.transactionamount
and d.transactiontype ='d'
where c.transactiontype='c'

However what I am finding is it is bringing back partial credits back from the merchant where there is a debit and I don't want there to be any corresponding debit

Any help would be appreciated

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-01-26 : 10:25:17

Add to your where clause:

and d.accountdetailid IS NULL

As long as you have your join clause bringing back unique records (apart from the transactiontype), this should do the trick..

aivoryuk
Starting Member

9 Posts

Posted - 2011-01-26 : 11:21:45

quote:
Originally posted by RickD

Add to your where clause:

and d.accountdetailid IS NULL

As long as you have your join clause bringing back unique records (apart from the transactiontype), this should do the trick..

Hi thanks for that it still seems to be bringing back credits with the same merchant where there is a less amount so I added this into the query

left outer join DebitCardTransactionFinancial as d with (nolock)
on c.accountdetailid=d.accountdetailid
and c.merchant=d.merchant
and c.transactionamount=d.transactionamount
and d.transactiontype ='d'
where c.transactiontype='c'
and d.accountdetailid IS NULL

this has reduced the numbers but this is notbring me back records where there are credits but no debits from the same merchant.

Any further ideas

That has reduced

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-26 : 11:32:59

for finding out merchants with no debits but only credits you can just do like


SELECT merchant
FROM table
GROUP BY merchant
HAVING MIN(transactiontype) = MAX(transactiontype)
AND MIN(transactiontype)='c'

if you want other details also join from your main query to above on merchant

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aivoryuk
Starting Member

9 Posts

Posted - 2011-01-26 : 13:55:05

quote:
Originally posted by visakh16

for finding out merchants with no debits but only credits you can just do like

SELECT merchant
FROM table
GROUP BY merchant
HAVING MIN(transactiontype) = MAX(transactiontype)
AND MIN(transactiontype)='c'

if you want other details also join from your main query to above on merchant

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi I am not sure if that works unless I am missing something I ahve created an example

create table #tmprefunds2(
accountid INT NOT NULL
,merchant varchar(30)
,transactionamount money not null
,transactiontype varchar (1) not null)

insert into #tmprefunds2(accountid,merchant,transactionamount,transactiontype) Values ('1234','p&o',200,'d')
insert into #tmprefunds2 (accountid,merchant,transactionamount,transactiontype) Values ('1234','p&o',200,'C')
insert into #tmprefunds2 (accountid,merchant,transactionamount,transactiontype) Values ('1234','ryanair',200,'C')
insert into #tmprefunds2 (accountid,merchant,transactionamount,transactiontype) Values ('23564','ryanair',200,'d')
insert into #tmprefunds2 (accountid,merchant,transactionamount,transactiontype) Values ('78925','easyjet',200,'C')

SELECT merchant
FROM #tmprefunds2
GROUP BY merchant
HAVING MIN(transactiontype) = MAX(transactiontype)
AND MIN(transactiontype)='c'

This only brings me back the merchant easyjet

what I would like it to do is for accountid '1234' should also bring back ryanair as there is no corresponding debit for it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-27 : 12:14:43

[code]
SELECT accountid,merchant
FROM #tmprefunds2
GROUP BY accountid,merchant
HAVING MIN(transactiontype) = MAX(transactiontype)
AND MIN(transactiontype)='c'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources