×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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?

CODE --> sql

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' 

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

Is that how your data looks like:

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

If so, it is not really normalized.

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

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


---- Andy

There is a great need for a sarcasm font.

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

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

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

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


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

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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close