I have the following query, I would like to modify it so in my count, instead of movies, it is values without an underscore. For the second count, I would like values that contain underscores.
Here is the original query:
select document
, (count(distinct (case when table_name = 'movies' then keyword else null end)) * 100)
+ (count(distinct (case when table_name = 'movie_summaries' then keyword else null end)) * 50)
as weight
from indexes where keyword in('the', 'hobbit')
group by document
order by weight desc limit 20
The top 3 weights are as follows:
- 300
- 250
- 200
I modified it, so it would look like this, and it didn't seem to work:
select document
, (count(distinct (case when table_name not like '%_%' then keyword else null end)) * 100)
+ (count(distinct (case when table_name like '%_%' then keyword else null end)) * 50)
as weight
from indexes where keyword in('the', 'hobbit')
group by document
order by weight desc limit 20
Now the top 3 weights are:
- 100
- 100
- 100
What is causing this to change?
CREATE TABLE `indexes` (
`keyword` CHAR(15) NOT NULL,
`document` INT(10) NOT NULL,
`position` INT(11) NOT NULL,
`table_name` CHAR(15) NOT NULL,
PRIMARY KEY (`keyword`, `document`, `position`, `table_name`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;
