Join on like statement not working when more than one value in field 2

[makeitwork09]

I'm bit confused with the query below.

Here is an example of the data set.

PURGE_ACTION LOAN CLOSEOUT_STATUS PREVIOUSLOANNUM
PURGE 3610878 REFINANCED
PURGE 3610887 REFINANCED
KEEP 3728285 REFINANCED 3610878 3610887

Note, the above example does not have all the columns. Also not that the query works as expected when only one value is in the PREVIOUSLOANNUM column.

The result I want is to return the first two rows above because the loan number is in the KEEP record's PREVIOUSLOANNUM.

The issue is the code is only returning the second row for loan 3610887. Is my code incorrect or is this because of the way SQL reads records?

select * 
from ##base b 
join (
	select b2.PREVIOUSLOANNUM
	from ##base b2
	where b2.PURGE_ACTION = 'KEEP'
    and b2.CLOSEOUT_STATUS = 'REFINANCED') as k on 
	k.PREVIOUSLOANNUM like '%' + b.loan + '%'
where b.PURGE_ACTION = 'PURGE'
and b.CLOSEOUT_STATUS = 'REFINANCED'

 

[Andrzejek]

Is that how your data looks like:

[pre]
PURGE_ACTION LOAN CLOSEOUT_STATUS PREVIOUSLOANNUM
PURGE 3610878 REFINANCED
PURGE 3610887 REFINANCED
KEEP 3728285 REFINANCED 3610878 3610887[/pre]

If so, it is not really normalized.

Consider (if you can):
[pre]
PURGE_ACTION LOAN CLOSEOUT_STATUS
PURGE 3610878 REFINANCED
PURGE 3610887 REFINANCED
KEEP 3728285 REFINANCED
[/pre]
and another table:[pre]
LOAN PREVIOUSLOANNUM
3728285 3610878
3728285 3610887[/pre]

Your Selects may be a lot easier this way...


---- Andy

There is a great need for a sarcasm font.

 

[makeitwork09]

Thanks Andrezjek - I have no control over that. The database was created by the vendor. The loan column has to be unique.

 

[johnherman]

I suggest a UNION query. First part of the UNION would SELECT the original load numbers and second SELECT would pick up the PreviousLoanNum

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[Olaf Doschke]

k.PREVIOUSLOANNUM like '%' + b.loan + '%' might just not work, because your fields are not varchar and even if they are, that doesn't mean v.loan automatically is trimmed without spaces. Varchar stores what's put into it in variable length, including spaces. For example, you won't find 3610878 within 3610878 3610887 when there are spaces before and after 3610878 and not in the combined data column. So k.PREVIOUSLOANNUM like '%' + TRIM(b.loan) + '%' could work.

Another reason could be loan is int and PREVIOUSLOANNUM is a char type.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[makeitwork09]

Thank You! Combining the union suggested by johnherman and the trim by Olaf Doschke I got it to work.