INTERSECT replacement in MySQL

Question

I’m trying to make this query MySQL compatible:

SELECT stories.*,
  (SELECT id FROM models WHERE model_user_id = stories.user_id AND user_id = ? LIMIT 1) 
  AS c_model_id
FROM stories
WHERE
  c_model_id IS NOT NULL
  AND
  EXISTS (
   SELECT tag_id FROM stories_tags WHERE story_id = stories.id
   INTERSECT
   SELECT tag_id FROM models_tags WHERE model_id = c_model_id
  )

I’ve tried the following according to this question.

SELECT 
  stories.*,
  (SELECT id FROM models WHERE model_user_id = stories.user_id AND user_id = ? LIMIT 1)
  AS c_model_id
FROM stories
WHERE
  c_model_id IS NOT NULL
  AND
  EXISTS (SELECT t1.tag_id FROM (
    SELECT tag_id FROM stories_tags WHERE story_id = stories.id -- Here
    UNION ALL
    SELECT tag_id FROM models_tags WHERE model_id = c_model_id
  ) AS t1 GROUP BY tag_id HAVING COUNT(*) >= 2)

However I’ve ran into a new problem: I can’t access the stories table from the deepest subquery: Unknown column 'stories.id' in 'where clause'

I was not able to find any solution to this problem, but likely there is one and I just don’t see it.

Edit:

I found a MySQL compatible query:

SELECT stories.*
FROM stories
INNER JOIN models ON model_user_id = stories.user_id AND models.user_id = ?
WHERE EXISTS
(
 SELECT stories_tags.tag_id
 FROM stories_tags
 LEFT JOIN models_tags
 ON models_tags.tag_id = stories_tags.tag_id
 WHERE models_tags.model_id = models.id AND story_id = stories.id
)

I think it is equal to the first query. Could somebody please verify this for me?

With `c_model_id` in the `WHERE` clause and in the subquery in the `WHERE` clause, are you referring to the subquery in the `SELECT` clause? If yes, then this won't work either. — fancyPants, Oct 29 '15 at 10:44
@fancyPants Yes, they intended to refer to the same. Actually I found an alternative query which, probably you could tell if it is really an substitute. — idmean, Oct 29 '15 at 11:43

Thorsten Kettner · Answer 1 · 2015-10-29T12:18:22.663

Your INTERSECT says: give me only those records for which a matching tag exists in both sets. So you can just as well join the tables by tag and search then:

SELECT 
  stories.*,
  (SELECT id FROM models WHERE model_user_id = stories.user_id AND user_id = ? LIMIT 1) 
    AS c_model_id
FROM stories
WHERE EXISTS
(
  SELECT * 
  FROM stories_tags st
  JOIN models_tags mt ON mt.tag_id = st.tag_id
  WHERE st.story_id = stories.id
  AND mt.model_id = stories.c_model_id
);

I removed WHERE c_model_id IS NOT NULL by the way, for in order to match a record in models_tags this must be true anyhow.

As two your own suggestion: Your outer join (LEFT JOIN) is no outer join at all, because in your WHERE clause you say you only want matches (WHERE models_tags.model_id = models.id) thus turning the join into an inner join. And this looks exactly what I've come up with :-) But you also changed your SELECT clause. Rather than picking the user model entry in case it exists, you inner-join the models table now. So in case that record doesn't exist, you get no data now. You could solve this by outer-joining models instead: LEFT JOIN models. And in case there is more than one match, you'll get duplicate results now. Whether it is possible not to get exactly one record from the subquery I don't know, however. Maybe it isn't, then your query would work fine.

INTERSECT replacement in MySQL

1 Answers1