SQL Query on four tables with MySQL - 'intersection'

Question

I have 4 tables

POST:
id

POST_TAG:
post_id
tag_id
value

TAG:
id

SEARCH:
tag_id
post_tag_value

I need to query for posts who have all tags and values as rows in SEARCH table ( not just one equal value for a tag):

EDIT: Sorry for not providing current query and enough information.

SELECT POST.id FROM POST,POST_TAG, SEARCH
WHERE
      POST.id = POST_TAG.post_id AND
      POST_TAG.tag_id= SEARCH.tag_id AND
      POST_TAG.value = SEARCH.value;

It works if SEARCH table has one row. The problem is, when it has more. There should be less results, but there are actually more( if test with 2 rows, the proper results are the rows that are duplicated; I am lookng for intersection not union)

Added sqlfiddle: http://sqlfiddle.com/#!2/9cfb9/1

The result from the query is '1','1','2' . It should be only '1', because it has both 'tags' while '2' has only one.

Great. Let's see what query you've got so far, and someone will help you out (edit it into your question, rather than adding it to the comments). — halfer, Jun 20 '12 at 13:35
Read this question with a lot of ways to do this: [How to filter SQL results in a has-many-through relation](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation) — ypercubeᵀᴹ, Jun 20 '12 at 13:38
Not clear requirement much. Show sample data and required output. — Somnath Muluk, Jun 20 '12 at 13:44

aF. · Answer 1 · 2012-06-20T16:38:31.340

2

According to your sqlfiddle, the answer might be this:

-- i want to select post that match to EVERY tag
-- the result of example data should be only '1'
SELECT POST.id as 'tag_id'
FROM POST,POST_TAG, SEARCH
WHERE
      POST.id = POST_TAG.post_id AND
      POST_TAG.tag_id= SEARCH.tag_id AND
      POST_TAG.value = SEARCH.value
GROUP BY POST.id
having COUNT(distinct POST_TAG.tag_id) = (select count(distinct tag_id) from POST_TAG);

edited Jun 20 '12 at 16:38

answered Jun 20 '12 at 13:42

aF.

64,980
43
135
198

This work, but creates a UNION of results instead an INTERSECTION – gabberr Jun 20 '12 at 14:37
1

i want to select post that match to every tag the result of example data should be only '1' http://sqlfiddle.com/#!2/393eb/1 – gabberr Jun 20 '12 at 16:31
@2fingers I've edit my answer, you may test it. Is this what you want? – aF. Jun 20 '12 at 16:38
You answer was useful. I just need to modify the last line so it counts tag_id from the SEARCH table. I don't know if this is the optimal query but it sure works. Thanks! – gabberr Jun 20 '12 at 17:11
1

if one day there is a post having more tags than the search is looking for it won't work, i guess you already knew that but i felt like reminding it. – Sebas Jun 20 '12 at 17:20
@Sebas you are right, initially I've used `TAG` table but it wouldn't work the way OP wanted. – aF. Jun 21 '12 at 08:37

score 2 · Accepted Answer · answered Jun 20 '12 at 17:46

2

working example: http://sqlfiddle.com/#!2/393eb/39

SELECT pt.post_id
FROM SEARCH s INNER JOIN post_tag pt ON pt.tag_id = s.tag_id AND pt.value = s.value
GROUP BY pt.post_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM SEARCH)

Please note that in your fiddle the post with id 0 should be returned as well since it has both (0,'yes') and (1, 'yes') tuples.

answered Jun 20 '12 at 17:46

Sebas

21,192
9
55
109

Point taken, 0 and 1 should be returned yes, so this is the proper way. – gabberr Jun 20 '12 at 19:08

SQL Query on four tables with MySQL - 'intersection'

2 Answers2