CODEWITHSUNDEEP
mysqlsql
beauchette
beauchette

Reputation: 1126

SQL: many to many relationships select where multiple criteria

given these tables :

id_article | title
1          | super article
2          | another article

id_tag | title
1      | great
2      | awesome

id_relation | id_article | id_tag
1           | 1          | 1
2           | 1          | 2
3           | 2          | 1

I'd like to be able to select all articles that are "great" AND "awesome" (eventually, I'll probably have to implement OR too)

And basically, if I do a select on articles the relation table joining on id_article: of course, I cant join two different values of id_tag. Only lead I had with concatenating IDs to test as a string, but that seems so lame, there has to be a prettier solution.

Oh and if it matters, I use a MySQL server.

EDIT: for ByWaleed, the typical sql select that would surely fail that I cited in my original question:

SELECT
    a.id_article,
    a.title
FROM articles a, relations r
WHERE
    r.id_article =  a.id_article and r.id_tag = 1 and r.id_tag = 2

wouldnt work because r.id_tag cant obviously be 1 and 2 on the same line. I doubt w3schools has an article on that. My search on google didnt yield any result, probably because I searched with the wrong keyword.

Upvotes: 4

Views: 3920

Answers (3)

Eray Balkanli
Eray Balkanli

Reputation: 7960

Step 1: Use a temp table to get all articles with titles.

Step 2: If an article occurs multiple times in your temp table, that means it has great and awesome as titles.

Try:

CREATE TEMPORARY TABLE MyTempTable (
    select t1.id_article, t2.title
    from table1 t1
    inner join table3 t3 on t3.id_article = t1.id_article
    inner join table2 t2 on t2.id_tag = t3.id_tag
)

select m.id_article
from MyTempTable m
group by m.id_article
having count(*)>1

Edit: This solution assumes there are two possible tags, great and awesome. If more, please add a "where" clause to the select query for creating the temp table like where t2.title in ('great','awesome')

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520938

One approach is to aggregate by article, and then assert that the article both the "great" and "awesome" tags:

SELECT
    a.id_article,
    a.title
FROM articles a
INNER JOIN relations r
    ON a.id_article = r.id_article
INNER JOIN tags t
    ON r.id_tag = t.id_tag
WHERE
    t.title IN ('great', 'awesome')
GROUP BY
    a.id_article,
    a.title
HAVING
    MIN(t.title) <> MAX(t.title);

enter image description here

Demo

The logic here is that we first limit records, for each article, to only those of the two targets tags. Then we assert, in the HAVING clause, that both tags appear. I use a MIN/MAX trick here, because if the min and max differ, then it implies that there are two distinct tags.

Upvotes: 1

MatBailie
MatBailie

Reputation: 86706

If you do all the joins as normal, then aggregate the rows to one group by article, then you can assert that they must have at least two different tags.

(Having already filtered to great and/or awesome, that means they have both.)

SELECT
    a.id_article,
    a.title
FROM
    articles a
INNER JOIN
    relations r
        ON r.id_article = a.id_article
INNER JOIN
    tags t
        ON t.id_tag = r.id_tag
WHERE
    t.title IN ('great', 'awesome')
GROUP BY
    a.id_article,
    a.title
HAVING
    COUNT(DISTINCT t.id_tag) = 2

(The DISTINCT is to avoid the possibility of one article having 'great' twice, for example.)

To do OR, you just remove the HAVING clause.

Upvotes: 7

Related Questions