2

I have 3 tables, resuming them:

    :::NEWS:::
ID  |  TITLE 
 1  |  New A  
 2  |  New B  

:::TAGTONEW:::
NEWID |  TAGID
  1   |  1 
  1   |  2
  2   |  2
  2   |  3 

:::TAGS:::
ID  |  TAG
 1  |  religion 
 2  |  sport
 3  |  politic

And the goal of this is to search news that match a given list of tags (e.g. News with the tags sport and religion). Ok. The problem is that when I do something like:

SELECT * FROM news JOIN tagtonew ON news.id = newid JOIN tags ON tagid = tags.id
WHERE tag IN ('religion','sport');

The result will be 3 rows, 2 of them repeating the New A. Ok, I can use GROUP BY news.id to get just one row, but the problem is that I need to return ALL the tags that match with New A (I need to return that New A is related to sport and religion), how can I do that? Requering the newid? Thanks in advance.

edit

The one above will result:

New A - religion
New A - sport
New B - sport

If I group will be like:

New A - religion
New B - sport

But I NEED to know that A was also related to sport, because I need to return it in a JSON. So I need the output to be something like:

New A - (religion, sport)
New B - sport
John Woo
  • 258,903
  • 69
  • 498
  • 492
João Menighin
  • 3,083
  • 6
  • 38
  • 80

1 Answers1

2

you just need to use GROUP BY and HAVING clause.

SELECT  a.Title
FROM    news a
        INNER JOIN tagtonew b
            ON a.id = b.newid 
        INNER JOIN tags c 
            ON b.tagid = c.id
WHERE   c.tag IN ('religion','sport')
GROUP BY a.Title
HAVING COUNT(*) = 2

UPDATE 1

use GROUP_CONCAT

SELECT  a.MovieName, '(' || GROUP_CONCAT(b.CategoryName) || ')' AS List
FROM    MovieList a
        INNER JOIN CategoryList b
            ON a.ID = b.MovieID
WHERE   b.CategoryName IN ('Comedy','Romance')
GROUP BY a.MovieName
Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492