sql select - need to pull back job with all keywords

[philipdowling]

Hi All

Total newbie here so apologies in advance for silly question.

I am working with a mysql database.
In this database I have 2 tables (well more than that but thats not
important), these being; "tbl_jobs" and "tbl_keywords".
Each job has one to many keywords associated.
Each job has a job_id.
Each of the keywords is associated to a job by referencing the job_id.

What I want to be able to do is to pass in multiple keywords, and
select only jobs which have all of these keywords.

I thought this sounded easy but then realised that I cannot just use a
straight AND i.e:

SELECT * from tbl_jobs
INNER JOIN tbl_keywords
ON tbl_jobs.job_id = tbl_keywords.job_id
WHERE tbl_keywords.keyword = 'full-time' AND tbl_keywords.keyword=
'pension'

as this is attempting to look for a keyword which is both equal to
"full-time" and equal to "pension".
This is obviously going to return 0 rows as it is impossible for the
keyword "pension" to also be equal to "full-time"?

I hope this makes sense?

So can anyone point me in the right direction as to how to tackle
this?


Thanks

 

[Guest]

Phil,

I'm not sure I understand your table structure, so I'll give you an example
using a query I've run in the past. I had two tables:

tbl_Job (Job_ID, Job_Desc)
tbl_KeyWords(KeyWord_ID, KeyWord)

tbl_KeyWords only contains the keywords I am searching for, not a complete
list of keywords for each of the jobs.

You will need something like:
SELECT DISTINCT Job_ID
FROM (
SELECT Job_ID, KeyWord_ID
FROM tbl_Jobs, tbl_KeyWords
WHERE instr(tbl_Jobs.Job_Desc, tbl_KeyWords.keyWord) > 0) as JobWordMatch
GROUP BY Job_ID
HAVING Count(KeyWord_ID) = (SELECT COUNT(*) FROM tbl_KeyWords)

This example looks for matches between the keywords and the Job_Desc. The
subquery selects one record for each job_Desc/keyword match. The outer query
aggregates the results of the subquery and only selects those job_IDs that
match all of the keywords in tbl_KeyWords.

If, on the other hand, your tbl_KeyWords contains fields like Job_ID and
KeyWord, then you have to figure out a way to store the key words you are
looking for.

HTH
Dale