CODEWITHSUNDEEP
oracle-database
Thillai Narayanan
Thillai Narayanan

Reputation: 4896

Oracle join query with like clause

I need to fetch a result with the combination of two tables.

select template_id 
from templatetbl 
where template_xml like '%889d-5765405edb42%'

On quering I am getting 24 records.

Now I want to fetch another set of records with the template id which I got earlier.

select * 
from sites 
where site_xml like '%templateid%'.

Templateid is the one which I got earlier.

I tried query like this 

select * 
from sites 
where site_xml like (select template_id 
                     from templatetbl 
                     where template_xml like '%889d-5765405edb42%')

But getting error as single-row subquery returns more than one row .

Please help on to combine both the queries

Upvotes: 2

Views: 830

Answers (2)

Sodved
Sodved

Reputation: 8598

This should do it. A simple join where you build the LIKE string dynamically.

SELECT S.*
FROM   templatetbl T, sites S
WHERE  T.template_xml LIKE '%889d-5765405edb42%'
AND    S.site_xml     LIKE '%'||TO_CHAR(T.templateid)||'%'

Edit: Be warned though, this is probably no what you want. Because templateid of 1 will match 1, 10, 11,... 21,... 100 etc

Upvotes: 0

Lokesh
Lokesh

Reputation: 7940

Try this:

select * 
from sites 
where site_xml like (select '''%'||template_id||'%'''
                     from templatetbl 
                     where template_xml like '%889d-5765405edb42%')

Upvotes: 1

Related Questions