CODEWITHSUNDEEP
mysqlsqlselectjointags
pandronic
pandronic

Reputation: 651

Complex SELECT query in MySQL

I have the following tables:

articles: id, title, content
tags: id, tag, tagCategory
tags2articles: id, idTag, idArticle
categories: id, title, someOtherFields

In a page I need to select all the articles that have a number of tags. I'm using this:

SELECT
   SQL_CALC_FOUND_ROWS a.* 
FROM 
  articles AS a
  JOIN tags2articles AS ta  ON a.id=ta.idArticle
  JOIN tags AS t ON ta.idTag=t.id
WHERE 
  t.id IN (12,13,16) 
GROUP BY a.id
HAVING
  COUNT(DISTINCT t.id)=3

This will select all the articles that have the tags with the IDs 12,13 and 16 and it works fine. But, the selected articles might also have other tags that might only be specific to one or more of them.

And here comes the tricky part: I want to use these tags to make some filters, so I need another query to select all the distinct tags that the articles above have. Something like this:

╔═══════╦══════╦═══════════╦════════════════╗
║ TagID ║ Tag  ║ Category  ║ SomeOtherField ║
╠═══════╬══════╬═══════════╬════════════════╣
║ id1   ║ tag1 ║ category1 ║ field1         ║
║ id2   ║ tag2 ║ category2 ║ field2         ║
║ id3   ║ tag3 ║ category1 ║ field1         ║
║ id4   ║ tag4 ║ category3 ║ field3         ║
╚═══════╩══════╩═══════════╩════════════════╝

Upvotes: 2

Views: 3284

Answers (3)

wildplasser
wildplasser

Reputation: 44250

This may look ugly, but it will probably be faster

SELECT a.*
FROM articles AS a
WHERE 1=1
AND EXISTS (
  SELECT *
  FROM tags2articles AS ta   
  JOIN tags AS t ON ta.idTag=t.id
  WHERE  a.id=ta.idArticle AND t.id = 12
  )
AND EXISTS (
  SELECT *
  FROM tags2articles AS ta  
  JOIN tags AS t ON ta.idTag=t.id
  WHERE  a.id=ta.idArticle AND t.id = 13
  )
AND EXISTS (
  SELECT *
  FROM tags2articles AS ta  
  JOIN tags AS t ON ta.idTag=t.id
  WHERE  a.id=ta.idArticle AND t.id = 16
  )
  ;

BTW: the joins with tha tags-tables can probably be omitted (given the proper FK constrants) since ta.idTag=t.id.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This is a rewrite of Michael's answer, removing extraneous joins:

SELECT DISTINCT t.id, t.tag, c.title AS Category
FROM tags2Articles t2a INNER JOIN
     tags t
     ON t.id = t2a.idTag INNER JOIN
     categories c ON t.tagCategory = c.id inner join
     /* Subquery join returns article ids having all 3 tags you filtered */
     /* Joining against tags2articles again will get the remaining tags for these articles */
     (SELECT t2a.idArticle
      FROM tags2articles t2a
      WHERE t2a.idTag IN (12,13,16) 
      GROUP BY t2a.idArticle
      HAVING COUNT(DISTINCT t2a.idTag)=3 
    ) asub
    ON t2a.idArticle = asub.idArticle

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270617

Using a similar query as you already have as a derived table to join against (but without all the a.* columns), you may do an INNER JOIN against tags2articles to get the remaining tags that those article ids have.

This should result in a distinct list of all tags held by any of the matched articles.

SELECT 
  DISTINCT
  t.id,
  t.tag, 
  c.title AS Category
FROM
  tags2Articles t2a 
  INNER JOIN tags t ON t.id = t2a.idTag
  INNER JOIN categories c ON t.tagCategory = c.id
  /* Subquery join returns article ids having all 3 tags you filtered */
  /* Joining against tags2articles again will get the remaining tags for these articles */
  INNER JOIN (
    SELECT
     a.id 
    FROM 
     articles AS a
     JOIN tags2articles AS ta  ON a.id=ta.idArticle
     JOIN tags AS tsub ON ta.idTag=tsub.id
    WHERE 
      tsub.id IN (12,13,16) 
    GROUP BY a.id
    HAVING COUNT(DISTINCT tsub.id)=3 
  ) asub ON t2a.idArticle = asub.id

Upvotes: 5

Related Questions