CODEWITHSUNDEEP
sqloracle-database
dummker
dummker

Reputation: 325

Oracle SQL select where like value of row

So I have 2 tables:

festival table: with column NAME 

---------------------
NAME
---------------------
1. ultra music
2. coachella

and Querydata with column: QUERY

----------------------
QUERY
----------------------
1. xyz
2. coachella festival
3. abc
4. ultra music festival

So I want to count all the occurrences of each festival in the querydata table.

I mean I could go through the Querydata table and search for every single festival name on it's own but I'm really looking for convenient way to do this.

SELECT QUERY FROM QUERYDATA 
WHERE QUERY LIKE '%coachella%'
AND QUERY LIKE '%festival%'

Please help me I'm really stuck on this one ! Cheers

Upvotes: 0

Views: 1924

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You could do something like this:

select f.name, count(*)
from querydata qd join
     festivals f
     on qd.query like '%' || f.name || '%'
group by f.name;

Note: This assumes that the names don't have overlaps. "Christmas" and "Christmas Eve" could result in unexpected duplicates.

Upvotes: 3

Kacper
Kacper

Reputation: 4818

Use like as mentioned in other answer or instr like below:

select t1.name, count(*) 
 from festival t1 inner join Querydata t2 on instr(t2.query, t1.name) > 0 
  group by t1.name;

Upvotes: 1

Related Questions