Question 257520 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL – table with multiple rows

I have table like this:

news_to_category

news_id category_id
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
4 1
4 2
4 4
5 2
5 3
5 4

And now I need to get all news_id containing category_id equals 1 and 2, so the result should be:

1, 2, 3, 4

(news 5 has 2 category, but doesn’t have 1 category)

How can I create SQL for this problem.

#mysql

I have tried SELECT * FROM news_to_category WHERE news_id IN (SELECT DISTINCT FROM news_to_category WHERE category_id = 1) AND news_id IN (SELECT DISTINCT FROM news_to_category WHERE category_id = 2)

Tags:

2

Answers


  1. 0

    Try this one:

    SELECT * FROM news_to_category
    WHERE category_id IN (1,2)
    GROUPBY news_id

    Login or Signup to reply.
  2. 0

    Common logical division:

    SELECT news_id
FROM news_to_category
WHERE category_id IN (1, 2)            -- categories list
GROUP BY 1
HAVING COUNT(DISTINCT category_id) = 2 -- categories list length

    If (news_id, category_id) is defined as UNIQUE then DISTINCT can be removed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search