Re: Difficulty with SELECT

[Michael Stassen]

Carsten Heidmann wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
I'm having trouble composing the right SQL statement for my purpose:
I am building a search interface to a database with research projects. The
research projects (tbl_projects) have assigned keywords in german and
english, which are in a seperate table (tbl_keywords). Because I want to
build a thesaurus from the keywords further on, I try to keep the number of
keywords as small as possible and assign the keywords to the projects via a
third table (tbl_keywords_projects).
When querying the database, I want to receive resultset which contains
projects which have all of the entered keywords assigned.
I started with:

SELECT
  `tbl_projects`.`project_id`,
FROM
  `tbl_projects`,
  `tbl_keywords_projects`,
  `tbl_keywords`
WHERE
    `tbl_projects`.`project_id` = `tbl_keywords_projects`.`project_fid`
  AND
    `tbl_keywords_projects`.`keyword_fid` = `tbl_keywords`.`keyword_id`
  AND (
      `tbl_keywords`.`keyword_de` IN ('keyword_1', 'keyword_2')
    OR
      `tbl_keywords`.`keyword_en` IN ('keyword_1', 'keyword_2')
  )
GROUP BY `tbl_projects`.`project_id`
but this returns all the datasets which contain at least one of the
keywords. I am looking for a SELECT statement which returns only the
datasets which contain all of the entered keywords. Since I am not very
familiar with complex SQL statements, I tried some other expressions which
always returned an empty result set.
I hope that this question is not _too_ stupid so that someone will have an
answer for me,
Carsten
You are very close.  Try

  SELECT tbl_projects.project_id AS id
  FROM tbl_projects, tbl_keywords_projects, tbl_keywords
  WHERE tbl_projects.project_id = tbl_keywords_projects.project_fid
  AND tbl_keywords_projects.keyword_fid = tbl_keywords.keyword_id
  AND (tbl_keywords.keyword_de IN (List_of_keywords)
    OR tbl_keywords.keyword_en IN (List_of_keywords))
  GROUP BY tbl_projects.project_id
  HAVING COUNT(*) = Number_of_keywords
Replace "List_of_keywords" and "Number_of_keywords" with appropriate 
values (For example, 'keyword_1', 'keyword_2' and 2).

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]