Re: SQL Question
Date: 1997/09/15
Message-ID: <341DBD25.3E21_at_NOSPAMmedicalert.org>#1/1
Lisa M. Lewis wrote:
>
> Hi all,
>
> I am trying to do something like:
>
> insert into temptable
> select userid
> from visits v, locations l
> where v.locationid = l.locationid
> and l.location like 'location1%'
>
> But instead of specifying a literal such as 'location1%', I would like
> to compare l.location to values held in a table. The key is I would
> still like to get the same results as a 'like' comparison would
> provide. This is what I did:
>
> insert into temptable
> select v.userid
> from visits v, location l, comparetable ct
> where v.location = l.location
> and SUBSTR(l.location,0,LENGTH( ct.location ) ) = ct.location;
The "0" in the substring should be "1".
> Can anyone tell me if there is a more efficient way of doing this either
> in SQL or PL/SQL?
Alternative:
insert into temptable
select v.userid
from visits v, location l, comparetable ct
where v.location = l.location
and l.location like ct.location
And load your comparetable with locations that you want to search for in the "like" format, i.e.
comparetable.location
location1%
location132%
this thing%
that thing
what%
%thingy
Since the like is acting as a join between your location table and the comparetable, you need to be careful that you don't place anything in the comparetable which would cause two hits for the same row on the location table:
Example:
location.location
location1 is the thingy
whatever is that thingy
location132
All these rows yeild two hits in the join to the comparetable, so they would generate two rows for your insert. The third one is deceptive - it yields a hit for location1% and location132%
- TRW -- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| | Tim Witort || He is not silent. He is not | | trwATmedicalertDOTorg || whispering. We are not quiet | | || we are not listening. | | Pin: TZ, only one :^( || - Out of the Grey | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||