Join query with multiple criteria

I have these tables

places(place_id, place_name)
places_criteria(place_id, criterion_id)
criteria(criterion_id, criterion_name)

"places_criteria" have foreign keys to "places" and "criteria". I can get many places by one criterion.

SELECT p.place_id, p.place_name
FROM places p INNER JOIN places_criteria pc ON p.place_id = pc.place_id
WHERE pc.criterion_id = < some_id >

How to get many places by multiple criteria?

For example: Disneyland is a place (place_id = 1), it is "good" (criterion_id = 1) and "interesting" (criterion_id = 2).

places_criteria's data:

place_id   criterion_id
1            1
1            2

Now I want to get the places which are "good" AND "interesting".

Answers


The hard coded version is to join the table on itself for each criterion you need...

SELECT
  c1.place_id
FROM
  places_criteria      AS c1
INNER JOIN
  places_criteria      AS c2
    ON c2.place_id = c1.place_id
WHERE
      c1.criterion_id = 1
  AND c2.criterion_id = 2

That is efficient, but requires dynamic SQL if you want to query a dynamic number of criterion_ids.

The alternative is to use IN (1,2) (or a join, etc, to perform a similar function) to get the answer to Places with criterion 1 <OR> 2, then using a HAVING clause to include only places with two different criterion.

SELECT
  place_id
FROM
  places_criteria
WHERE
  criterion_id IN (1,2)
GROUP BY
  place_id
HAVING
  COUNT(DISTINCT criterion_id) = 2

You can do this :

SELECT p.place_id, p.place_name
FROM places p, places_criteria pc1, places_criteria pc2, places_criteria pc3
WHERE p.place_id = pc2.place_id and pc1.criterion_id = < some_id >
and  p.place_id = pc2.place_id and pc2.criterion_id = < some_id >
and p.place_id = pc3.place_id and pc3.criterion_id = < some_id >

But I have a doubt regarding my understanding of your question : why do you show us the criteria table ?


If you want all the places that meet one of several criteria:

SELECT p.place_id, p.place_name
FROM places p
INNER JOIN places_criteria pc ON p.place_id = pc.place_id
WHERE pc.criterion_id = < some_id >
OR pc.criterion_id = < some_id2 >
OR pc.criterion_id = < some_id3 >
GROUP BY p.place_id, p.place_name

If you want all the places that meet all of several criteria:

SELECT p.place_id, p.place_name
FROM places p
INNER JOIN places_criteria pc1 ON p.place_id = pc1.place_id
INNER JOIN places_criteria pc2 ON p.place_id = pc2.place_id
INNER JOIN places_criteria pc3 ON p.place_id = pc3.place_id
WHERE pc1.criterion_id = < some_id >
AND pc2.criterion_id = < some_id2 >
AND pc3.criterion_id = < some_id3 >

Use in to select from a set. You can use a subselect to get the right place ids, or you can use distinct as I do, which is probably somewhat faster in MySQL.

select distinct
  p.place_id,
  p.place
from
  places p
  inner join places_criteria pc on pc.place_id = p.place_id
  inner join criteria c on c.criterion_id = pc.criterion_id
where
  /* Either by name */
  c.criterion_name in ('crit A', 'crit B', 'crit C')
  /* Or by id */
  OR c.criterion_id in (1, 2, 3, 4, 5)

Solution 2. Match all criteria. Do this, by checking the count.

select
  p.place_id,
  p.place
from
  places p
  inner join places_criteria pc on pc.place_id = p.place_id
  inner join criteria c on c.criterion_id = pc.criterion_id
where
  c.criterion_name in ('crit A', 'crit B', 'crit C')
group by 
  p.place_id,
  p.place
having
  count(*) = 3 /* the number of criteria */

Need Your Help

a null progressbar is created?

android xml progress-bar

When I run the code below it prints "null" in the log file... What is the problem with it?

KornShell script to get files between two dates

shell scripting file date ksh

Need to get the files between two given dates via a KornShell (ksh) script. If there are multiple files on one day get the latest of the files for that day.