CODEWITHSUNDEEP
mysql
dave.zap
dave.zap

Reputation: 511

MySQL search engine - AND keywords

I'm trying to make a search query between a table of key terms and pages.

Consider the following tables.

PAGES

page_id  page_name
1        cats
2        dogs
3        humans

KEYWORDS

key_id   key_name   key_page_id
1        purz       1
2        puzy       1
3        ruff       2
4        john       3
5        purz       3

Where the search terms are purz AND puzy I want the query results to return only page id 1.

SELECT page_id FROM PAGES, KEYWORDS 
WHERE (key_name='purz OR key_name='puzy') AND key_page_id = page_id;

Returns

page_id
1
3

I'm wanting 

page_id
1

Because only purz AND puzy both are linked to Cats.

Upvotes: 1

Views: 142

Answers (3)

Yasen Zhelev
Yasen Zhelev

Reputation: 4045

SELECT p.page_id, COUNT( key_id ) AS keyword_all
FROM PAGES p
JOIN KEYWORDS ON key_page_id = page_id
GROUP BY page_id
HAVING keyword_all = (
  SELECT COUNT( key_id ) AS found_k
    FROM KEYWORDS
    WHERE key_page_id = p.page_id
      AND (
      key_name = 'purz'
      OR key_name = 'puzy'
    )
  )

What I did here is to get only pages which total number of keywords match the keywords that we look for.

This way you do not need to join multiple times. Only change the WHERE condition in the subquery according to the keywords that you look for.

Upvotes: 0

Gryphius
Gryphius

Reputation: 78906

not sure if this really works, but you could try to join the keywords table with itself for each searchterm, eg something like:

SELECT searchterm1.key_page_id as page_id FROM KEYWORDS searchterm1 
INNER JOIN KEYWORDS searchterm2 
ON (searchterm1.key_page_id = searchterm2.key_page_id) 
WHERE searchterm1.key_name='purz' AND searchterm2.key_name='puzy';

you wouldn't even need to join with the PAGES table, unless you need page_name returned as well

Upvotes: 0

Jonathan Hall
Jonathan Hall

Reputation: 79594

I believe to accomplish this, you have to join on the keywords table twice:

SELECT p.page_id
FROM pages p
JOIN keywords k ON (k.key_page_id = p.page_id)
JOIN keywords k2 ON (k2.key_page_id = p.page_id)
WHERE k.key_name='purz'
    AND k2.key_name='puzy';

Upvotes: 1

Related Questions