Re: Like clause queries

From: Tim X <timx_at_spamto.devnul.com>
Date: 19 Jan 2003 09:37:31 +1100
Message-ID: <87lm1im6dw.fsf@tiger.rapttech.com.au>

>>>>> "Sam" == Sam <samevan_at_hotmail.com> writes:

 Sam> Tim X <timx_at_spamto.devnul.com> wrote in message
 Sam> news:<87el7bphtk.fsf_at_tiger.rapttech.com.au>...

>> >>>>> "Sam" == Sam <samevan_at_hotmail.com> writes:
>>

 Sam> I have two queries select T1_Field1 from T1 where T1_Field2 like  Sam> "deptname"
>>

 Sam> select * from T2 where T2_Field2 like T1_Field1
>>

 Sam> Is there anyway to combine these two queries to one?
>>
>> Not sure exactly what you are after here. However, reading between
>> the lines I'll have a couple of guesses.
>>
>> 1. You use 'like' - do you really mean like or do you mean
>> '='. Like
>> is used when you want to use wildcard matching (e.g. _ and %). As
>> you don't have any of these in your query, I suspect you might
>> just want =
>>
>> 2. If what you are trying to do is join the two tables and your
>> column
>> datatypes are the same and you want a result like
>>
>> T1.T1_field1, T2.T2_field1, ..., T2_fieldN
>>
>> then what you MIGHT be after is just a join between the two tables
>> based on T2.Field2 and T1.Field1
>>
>> select T1.T1_field1, T2.T2_Field1, .., T2.T2_FieldN from T1, T2
>> where T2.T2_Field2 = T1.T1_Field1 and T1.T1_Field2 = 'depname';
>>
>> If you have NULLs in either of your joining fields, you may need
>> to look into how to do an outer join.
>>
>> Tim

 Sam> Thank you for reply

 Sam> Have to use LIKE because the field is in a long string. By the  Sam> way it should be T1.T1_Field2 = '%depname%';

 Sam> The question here is how to join two queries with LIKE clause

 Sam> subqueries??? any ideas?

Well....

I guess you could use a sub-query. Something like

select *
from t2
where t2.field1 in (select t1.field1

                    from t1
                    where t1.field2 like '%depname%');

but its going to be very inefficient. Apart from doing the sub-select for each row in t2, its going to be a full table scan everytime because your '%depname%' is going to mean no index lookups. However, I think you should still be able to do it as a join, unless I've missed soemthing.

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!

Received on Sat Jan 18 2003 - 16:37:31 CST