Skip to main content
added additional answer where remove join of tags table if joining using tag id's
Source Link
amaster
  • 200
  • 1
  • 8

Fiddle: http://sqlfiddle.com/#!9/ea5723/9

Here is my plausible solution, but I think there might be a better answer out there. In this answer, I add multiple joins for every tag that needs to be compared. So if I am looking for 5 tags in common the query will have 10 join statements.

SELECT
  places.id AS p_id,
  places.name
FROM
  places
  JOIN place_taxonomy pt1
    ON places.id=pt1.place_id
  JOIN tags t1
    ON pt1.tag_id=t1.id AND t1.id=1
  JOIN place_taxonomy pt2
    ON places.id=pt2.place_id
  JOIN tags t2
    ON pt2.tag_id=t2.id AND t2.id=2
GROUP BY
  p_id;

The above query will give the response of Plan C which has both tags tag-z and tag-y

+------+--------+
| p_id | name   |
+======+========+
| 3    | Plan C |
+------+--------+

Update: Alternatively if selecting tags by id and not any other column then we can remove the tags join. At least then we only have one additional join for every relationship checking.

SELECT
  places.id AS p_id,
  places.name
FROM
  places
  JOIN place_taxonomy pt1
    ON places.id=pt1.place_id AND pt1.tag_id=1
  JOIN place_taxonomy pt2
    ON places.id=pt2.place_id AND pt2.tag_id=2
GROUP BY
  p_id;

Fiddle: http://sqlfiddle.com/#!9/ea5723/9

Here is my plausible solution, but I think there might be a better answer out there. In this answer, I add multiple joins for every tag that needs to be compared. So if I am looking for 5 tags in common the query will have 10 join statements.

SELECT
  places.id AS p_id,
  places.name
FROM
  places
  JOIN place_taxonomy pt1
    ON places.id=pt1.place_id
  JOIN tags t1
    ON pt1.tag_id=t1.id AND t1.id=1
  JOIN place_taxonomy pt2
    ON places.id=pt2.place_id
  JOIN tags t2
    ON pt2.tag_id=t2.id AND t2.id=2
GROUP BY
  p_id;

The above query will give the response of Plan C which has both tags tag-z and tag-y

+------+--------+
| p_id | name   |
+======+========+
| 3    | Plan C |
+------+--------+

Fiddle: http://sqlfiddle.com/#!9/ea5723/9

Here is my plausible solution, but I think there might be a better answer out there. In this answer, I add multiple joins for every tag that needs to be compared. So if I am looking for 5 tags in common the query will have 10 join statements.

SELECT
  places.id AS p_id,
  places.name
FROM
  places
  JOIN place_taxonomy pt1
    ON places.id=pt1.place_id
  JOIN tags t1
    ON pt1.tag_id=t1.id AND t1.id=1
  JOIN place_taxonomy pt2
    ON places.id=pt2.place_id
  JOIN tags t2
    ON pt2.tag_id=t2.id AND t2.id=2
GROUP BY
  p_id;

The above query will give the response of Plan C which has both tags tag-z and tag-y

+------+--------+
| p_id | name   |
+======+========+
| 3    | Plan C |
+------+--------+

Update: Alternatively if selecting tags by id and not any other column then we can remove the tags join. At least then we only have one additional join for every relationship checking.

SELECT
  places.id AS p_id,
  places.name
FROM
  places
  JOIN place_taxonomy pt1
    ON places.id=pt1.place_id AND pt1.tag_id=1
  JOIN place_taxonomy pt2
    ON places.id=pt2.place_id AND pt2.tag_id=2
GROUP BY
  p_id;
Source Link
amaster
  • 200
  • 1
  • 8

Fiddle: http://sqlfiddle.com/#!9/ea5723/9

Here is my plausible solution, but I think there might be a better answer out there. In this answer, I add multiple joins for every tag that needs to be compared. So if I am looking for 5 tags in common the query will have 10 join statements.

SELECT
  places.id AS p_id,
  places.name
FROM
  places
  JOIN place_taxonomy pt1
    ON places.id=pt1.place_id
  JOIN tags t1
    ON pt1.tag_id=t1.id AND t1.id=1
  JOIN place_taxonomy pt2
    ON places.id=pt2.place_id
  JOIN tags t2
    ON pt2.tag_id=t2.id AND t2.id=2
GROUP BY
  p_id;

The above query will give the response of Plan C which has both tags tag-z and tag-y

+------+--------+
| p_id | name   |
+======+========+
| 3    | Plan C |
+------+--------+