How to remove articles from categories?

Question

PHP

Good afternoon. There are tables:
blog
id
name
text
date
status
category
id
name
categorylist
id
category_id
blog_id
The categorylist table links articles to categories. One article can be placed in several categories.
How to make a selection of articles knowing the category in which it is located.
First, I select all article ids by category id:

SELECT `blog_id` FROM `categorylist` WHERE `category_id` = 1

I get blog_id into the list and then I output articles:

SELECT * FROM `blog` WHERE `id`  IN (1,2,3,4,5,6,7,8,9)

How else can you do it differently? If I have more than 100 articles, is it not too time consuming with IN?

Answer 1

Read about JOINs
something like this:

select b.* from blog b
join categorylist cl ON b.id = cl.blog_id
join category c ON c.id = cl.category_id
where ....

Answer 2

And so, in order:

select b.* from 269816_blog b
where b.id in 
(select cl.blog_id from 269816_categorylist cl where cl.category_id=1);

Where 1 is the category number you know .
Or

select b.* from 269816_blog b
where b.id in 
(select cl.blog_id from 269816_categorylist cl 
where cl.category_id=(select cat.id from 269816_category cat where cat.name=2001));

Where 2001 is the name of the category (I have numeric values ​​\u200b\u200bassigned in the database), which you also know .
Since the selection will be based on primary keys, this will not affect the speed of work in your current task.
to display all articles from the database with the categories to which they belong, you can use the following query:

select b.*, group_concat(c.id)
from 
269816_blog b,
269816_category c,
269816_categorylist cl
where cl.category_id=c.id
and cl.blog_id=b.id
group by b.id

We select articles, then we specify an association between categories, and then we bind categories to articles. As a result, it gets this:
Article 1 we have in 2 categories:
If the request is changed group_concat(c.id)to group_concat(c.name), then the result will be the names of the categories.