Fetch fields from a table that has the same relation to another table

Question

I'll try to explain my case as good as i can. I'm making a website where you can find topics by browsing their tags. Nothing strange there. I'm having tricky time with some of the queries though. They might be easy for you, my mind is pretty messed up from doing alot of work :P.

I have the tables "topics" and "tags". They are joined using the table tags_topics which contains topic_id and tag_id. When the user wants to find a topic they might first select one tag to filter by, and then add another one to the filter. Then i make a query for fetching all topics that has both of the selected tags. They might also have other tags, but they MUST have those tags chosen to filter by. The amount of tags to filter by differs, but we always have a list of user-selected tags to filter by. This was mostly answered in http://stackoverflow.com/questions/648308/filtering-from-join-table and i went for the multiple joins-solution.

Now I need to fetch the tags that the user can filter by. So if we already have a defined filter of 2 tags, I need to fetch all tags but those in the filter that is associated to topics that includes all the tags in the filter. This might sound wierd, so i'll give a practical example :P

Let's say we have three topics: tennis, gym and golf.

• tennis has tags: sport, ball, court and racket
• gym has tags: sport, training and muscles

• golf has tags: sport, ball, stick and outside

  1. User selects tag sport, so we show all three tennis, gym and golf, and we show ball, court, racket, training, muscles, stick and outside as other possible filters.
  2. User now adds ball to the filter. Filter is now sport and ball, so we show the topics tennis and golf, with court, racket, stick and outside as additional possible filters.
  3. User now adds court to the filter, so we show tennis and racket as an additional possible filter.

I hope I'm making some sense. By the way, I'm using MySQL.

Answer 1

SELECT   topic_id
FROM     topic_tag
WHERE    tag_id = 1
      OR tag_id = 2
      OR tag_id = 3
GROUP BY topic_id
HAVING   COUNT(topic_id) = 3;

The above query will get all topic_ids that have all three tag_ids of 1, 2 and 3. Then use this as a subquery:

SELECT tag_name
FROM   tag
       INNER JOIN topic_tag
ON     tag.tag_id = topic_tag.tag_id
WHERE  topic_id  IN
                     ( SELECT  topic_id
                     FROM     topic_tag
                     WHERE    tag_id = 1
                           OR tag_id = 2
                           OR tag_id = 3
                     GROUP BY topic_id
                     HAVING   COUNT(topic_id) = 3
                     )
   AND
       (
              tag.tag_id <> 1
           OR tag.tag_id <> 2
           OR tag.tag_id <> 3
       )

I think this is what you are looking for.

Answer 2

SELECT DISTINCT `tags`.`tag`
FROM `tags`
LEFT JOIN `tags_topics` ON `tags`.`id` = `tags_topics`.`tag_id`
LEFT JOIN `topics` ON `tags_topics`.`topic_id` = `topics`.`id`
LEFT JOIN `tags_topics` AS `tt1` ON `tt1`.`topic_id` = `topics`.`id`
LEFT JOIN `tags` AS `t1` ON `t1`.`id` = `tt1`.`tag_id`
LEFT JOIN `tags_topics` AS `tt2` ON `tt2`.`topic_id` = `topics`.`id`
LEFT JOIN `tags` AS `t2` ON `t2`.`id` = `tt2`.`tag_id`
LEFT JOIN `tags_topics` AS `tt3` ON `tt3`.`topic_id` = `topics`.`id`
LEFT JOIN `tags` AS `t3` ON `t3`.`id` = `tt3`.`tag_id`
WHERE `t1`.`tag` = 'tag1'
AND `t2`.`tag` = 'tag2'
AND `t3`.`tag` = 'tag3'
AND `tags`.`tag` NOT IN ('tag1', 'tag2', 'tag3')

Answer 3

Select a.topic_id
  from join_table a
 where exists( select *
                 from join_table b
                where a.tag_id = b.tag_id
                  and b.topic_id = selected_topic )
 group by a.topic_id
 having count(*) = ( select count(*)
                       from join_table c
                      where c.topic_id = selected_topic )

Should give you a list of topics which have all of the tags for selected_topic.

Answer 4

Generic solution from the top of my head but prone to have typos:

  CREATE VIEW shared_tags_count AS
  SELECT topic_to_tag1.topic_id AS topic_id1, topic_to_tag2.topic_id AS topic_id2, COUNT(*) as number
    FROM topic_to_tag as topic_to_tag1
         JOIN topic_to_tag as topic_to_tag2
         ON topic_to_tag1.topic_id <> topic_to_tag2.topic_id
             AND topic_to_tag1.tag_id = topic_to_tag2.tag_id
GROUP BY topic_to_tag1.topic_id, topic_to_tag2.topic_id;

  CREATE VIEW tags_count AS
  SELECT topic_id, COUNT(*) as number
    FROM topic_to_tag
GROUP BY topic_id

CREATE VIEW related_topics AS
SELECT shared_tags_count.topic_id1, shared_tags_count.topic_id2
  FROM shared_tags_count
       JOIN tags_count
         ON topic_id=topic_id1
            AND shared_tags_counts.number = tags_count.number

CREATE VIEW related_tags AS
SELECT related_topics.topic_id1 as topic_id, topic_to_tag.tag_id
  FROM related_topics
       JOIN topic_to_tag
       ON raleted_topics.tag_id2 = topic_to_tag.topic_id

You just have to query the related_tags view.

Interesting challenge btw.

Answer 5

can we select rows having the same value in a field?