MYSQL Query same column with AND and OR conditions

Question

I have the following tables:

       Topic                 Content_Topic                Content

id_topic   topic         id_content   id_topic     id_content  content
    1      aaaaa             1            2             1        xxxxx
    2      bbbbb             1            4             2        yyyyy
    3      ccccc             1            5             3        zzzzz
    4      ddddd             2            1             4        wwwww
    5      eeeee             2            3             5        kkkkk
    6      fffff             2            5             6        jjjjj
        ...                  3            3                  ...
                             3            4 
                             3            5 
                                  ...

I'm trying to run the following query but I'm not obtaining what I expect:

SELECT content FROM Content_Topic ct
LEFT JOIN Content c ON ct.id_content=c.id_topic
LEFT JOIN Topic t ON ct.id_topic=t.id_topic
WHERE   (ct.id_topic=2 OR ct.id_topic=3) AND 
        ct.id_topic IN (4,7,10) AND 
        (ct.id_topic=5 OR ct.id_topic=9)

What I expect is to have all content that have id_topic 2,4,5 or 3,4,5 or 2,7,5 or 3,7,5 and so on... I receive instead a void result.

What i'm doing wrong?

mikeb · Answer 1 · 2020-01-17T15:08:35.397

1

You're saying (2 or 3) AND (4 or 7 or 10) AND (5 or 9)

What that means is you'll never get any results.

y = 2 or 3

x = 4, 7 or 10

z = 5 or 9

It's impossible to make x AND y be true in that case.

I would use table aliases to reference your Content_Topic table 3 times so you can make each of your criteria work and I would use Joins and not sub-selects because it's faster:

SELECT content FROM Content c 
INNER JOIN Content_Topic ct1 ON ct1.id_content=c.id_topic AND (ct1.id_topic=2 OR ct1.id_topic=3)
INNER JOIN Content_Topic ct2 ON ct2.id_content=c.id_topic AND (ct2.id_topic IN (4,7,10))
INNER JOIN Content_Topic ct3 ON ct3.id_content=c.id_topic AND (ct3.id_topic=5 OR ct3.id_topic=9)

edited Jan 17 '20 at 15:08

answered Jan 17 '20 at 14:45

mikeb

10,578
7
62
120

If this way is not possible, do you know a different way to manage it? Thank you for your help – Luca Jan 17 '20 at 14:50
See my edits for what I think you want - it's untested and if you want a working example setup an sqlfiddle and provide a link – mikeb Jan 17 '20 at 15:03
*`FROM Content c .. ON .. =c.id_topic`* Where have you found it? No `id_topic` field in `Content` table. – Akina Jan 17 '20 at 15:45
This seems to works and seems the best solution. I did some corrections and changes: ct1.id_content=c.id_topic to ct1.id_content=c.id_content and I added a DISTINCT since there were duplicates. If ok and you modify your answer, I accept it as solution. SELECT DISTINCT(c.id_content), c.* FROM Content c INNER JOIN Content_Topic ct1 ON ct1.id_content=c.id_content AND (ct1.id_topic=2 OR ct1.id_topic=3) INNER JOIN Content_Topic ct2 ON ct2.id_content=c.id_content AND (ct2.id_topic IN (4,7,10)) INNER JOIN Content_Topic ct3 ON ct3.id_content=c.id_content AND (ct3.id_topic=5 OR ct3.id_topic=9) – Luca Jan 17 '20 at 16:59

Bill Karwin · Accepted Answer · 2020-01-17T17:32:02.837

1

It looks like you're doing a special type of relational division. That is, you're looking for content that matches a set of topics, where each element has alternates.

You can't do this only with a condition in a WHERE clause, because the condition is evaluated for one row at a time. There is no value of id_topic on a given row that is both 2 and 4 and 5 at the same time.

You need a condition on sets of rows, and this is what relational division is.

In your case, I'd do it with joins. By using joins, you can make three references to different rows of Content_Topic, so the conditions can be mutually exclusive.

SELECT c.content
FROM Content c
INNER JOIN Content_Topic ct1 
  ON c.id_content=ct1.id_content AND ct1.id_topic IN (2,3)
INNER JOIN Content_Topic ct2 
  ON c.id_content=ct2.id_content AND ct2.id_topic IN (4,7,10)
INNER JOIN Content_Topic ct3
  ON c.id_content=ct3.id_content AND ct3.id_topic IN (5,9)

This query must matches a content to three topics. If it doesn't match all three, the content won't be returned by the query.

But each of the three has alternates.

edited Jan 17 '20 at 17:32

answered Jan 17 '20 at 15:13

Bill Karwin

538,548
86
673
828

Also this query works with some correction. INNER JOIN Content_Topic ct1 should change in INNER JOIN Content_Topic ct2, INNER JOIN Content_Topic ct3... but I'm not sure if ct1.id_topic IN (2,3) is the same of (ct1.id_topic=2 OR ct1.id_topic=3) – Luca Jan 17 '20 at 17:27
Oh, you're right, it's a consequence of copying and pasting. I'll fix it. – Bill Karwin Jan 17 '20 at 17:31
Yes, the `IN(...)` predicate is exactly the same as several `OR` terms, assuming each term is comparing with `=`. – Bill Karwin Jan 17 '20 at 17:32
Thank you. Just to add a bit, I added also a DISTINCT since the query generates duplicates. – Luca Jan 17 '20 at 18:07

score 0 · Answer 3 · answered Jan 17 '20 at 14:59

I think that this would be easier done with aggregation and a HAVING clause for filtering:

SELECT c.content 
FROM Content c
INNER JOIN Content_Topic ct ON ct.id_content = c.id_content
GROUP BY c.id_content, c.content
HAVING
    -- 2, 4, 5
    (MAX(c.id_topic = 2) = 1 AND MAX(c.id_topic = 4) = 1 AND MAX(c.id_topic = 5) = 1)
    -- 3, 4, 5
    OR (MAX(c.id_topic = 3) = 1 AND MAX(c.id_topic = 4) = 1 AND MAX(c.id_topic = 5) = 1)
    -- 2, 5, 7
    OR (MAX(c.id_topic = 2) = 1 AND MAX(c.id_topic = 5) = 1 AND MAX(c.id_topic = 7) = 1)
    -- add more if needed ...

This will give you all contents that either have topics 2, 3 and 5, OR topics 3, 4, and 5, OR topics 2, 5 and 7. You can extend the HAVING clause with more OR conditions as needed.

Another thing to notice is that you don't actually need to bring in the Topic table for that task (the information you want is available in Content_Topic).

This works, but in HAVING cluase you are using the result as id_topics and I didn't know the result before doing the query — Luca, Jan 17 '20 at 16:45

Ankit Bajpai · Answer 4 · 2020-01-17T14:51:22.040

-1

I think your joining condition is going wrong -

SELECT content
FROM Content_Topic ct
LEFT JOIN Content c ON ct.id_content=c.id_content
WHERE EXISTS (SELECT 1
              FROM Topic T
              WHERE T.id_topic = ct.Content_Topic
              AND ct.id_topic IN (2, 3))
AND EXISTS (SELECT 1
            FROM Topic T1
            WHERE T1.id_topic = ct.Content_Topic
            AND ct.id_topic IN (4,7,10))
AND EXISTS (SELECT 1
            FROM Topic T2
            WHERE T2.id_topic = ct.Content_Topic
            AND ct.id_topic IN (5, 9))

edited Jan 17 '20 at 14:51

answered Jan 17 '20 at 14:46

Ankit Bajpai

13,128
4
25
40

Use joins instead of sub-selects: https://stackoverflow.com/questions/2577174/join-vs-sub-query – mikeb Jan 17 '20 at 15:03

score -1 · Answer 5 · answered Jan 17 '20 at 14:50

-1

SELECT *
FROM Content_Topic ct1
JOIN Content c1 ON ct`.id_content=c`.id_topic
JOIN ( SELECT id_content
       FROM Content_Topic ct
       LEFT JOIN Content c ON ct.id_content=c.id_topic
       LEFT JOIN Topic t ON ct.id_topic=t.id_topic
       GROUP BY id_content
       HAVING SUM(ct.id_topic IN (2,3)) > 0
          AND SUM(ct.id_topic IN (4,7,10)) > 0
          AND SUM(ct.id_topic IN (5,9)) > 0 
     ) sq ON ct.id_content = ct1.id_content

answered Jan 17 '20 at 14:50

Akina

39,301
5
14
25

Use joins instead of sub-selects: https://stackoverflow.com/questions/2577174/join-vs-sub-query – mikeb Jan 17 '20 at 15:03

MYSQL Query same column with AND and OR conditions

5 Answers5