Help with Delphi 7, ADO, & MS Access SQL Statement - Part Deuce

I need help understanding why my SQL does not work. Or, if i need to write it differently to get the results i need. As the title suggests, I am using Delphi 7, with ADO components, and a MS Access 2000 database. You can see my table structure from Part I here:

Help with Delphi 7, ADO, & MS Access SQL Statement

The SQL i am currently using to get all knowledge based on keywords is as follows:

select * from (knowledge K

inner join knowledge_keywords KKW on KKW.knowledgeid = K.id)

inner join keywords KW on KW.id = KKW.keywordid

where (KW.keyword = 'job') AND (KW.keyword = 'task')

However, this does not return and results, when there is clearly both of those words in the knowledge_keywords table with the same knowledge id.

However, if i do the same SQL with an OR instead of an AND, i get the two records i expected

select * from (knowledge K

inner join knowledge_keywords KKW on KKW.knowledgeid = K.id)

inner join keywords KW on KW.id = KKW.key开发者_运维知识库wordid

where (KW.keyword = 'job') AND (KW.keyword = 'task')

thanks for any help

Think about it this way: How many records are there in knowledge_keywords for which it is true both that keyword = 'job' AND keyword = 'task'. There are no such records. When you use AND you're asking for records that satisfy both the first condition AND the second condition at the same time. When you use OR, you're asking for records that satisfy one condition OR the other one (or both).

In this case, OR expresses what you want. AND expresses something different.

You can also use KW.keyword IN ('job', 'task') which is more concise and, perhaps, clearer.

I think the first query won't return any result, does it? That's because 'and' in speech differs from 'and' in programming. When you say, you want the keywords 'job' and 'task', you actually mean you want the rows where keyword is either 'job' or 'task'. A keyword cannot be both 'job' and 'task' so that query won't return any rows. You could replace the OR with an IN in the form of

WHERE KW.Keyword in ('job', 'task')

But this probably won't give you the result you want. I suspect you need to find articles that match both keywords. To check if a knowledgebase has both keywords, you might need something like this (although I'm not sure if Access accepts this:

    select
      * 
    from 
      knowledge K
    where
      exists
          (select 'x' from
            knowledge_keywords KKW
            inner join keywords KW on KW.id = KKW.keywordid
          where
            KKW.knowledgeid = K.id and
             KW.keyword = 'job') 
      and exists
          (select 'x' from
            knowledge_keywords KKW
            inner join keywords KW on KW.id = KKW.keywordid
          where
            KKW.knowledgeid = K.id and
             KW.keyboard = 'task') and

[edit]

A different approach, that might work better in Access (I'm sorry I can't test it) is by using a count like this. I made a small assumption about the fields in K for this example. This way, you join each keyword in the list. For a knowledge base article that has both 'job' and 'task' it will return two rows at first. These rows are then grouped on the Knowledge fields, and the rows are counted. Only the articles where count matches the total number of keywords are returned.

Possible problem: When an article has the same keyword (job) linked twice, it is still returned. This can be solved by preventing that from happening using unique constraints.

    select
      K.ID,
      K.Title,
      K.Content
    from
      knowledge K
      inner join knowledge_keywords KKW on KKW.knowledgeid = K.id)
      inner join keywords KW on KW.id = KKW.keywordid
    where 
      KW.keyword in ('job', 'task')
    group by
      K.ID,
      K.Title,
      K.Content
    having
      count(*) = 2 /* Number of keywords */