Query in MySQL. I have an idea?

Question

MySQL

Table structure:
id | link_id | flag

Sample data:
1 | 111 | 1
2 | 111 | 1
3 | 111 | 0
4 | 111 | 1
5 | 222 | 1
6 | 222 | 1
7 | 222 | 1
8 | 333 | 1
9 | 333 | 0

Essence of the problem:
Select all records with flag equal to 1 for all records with the same link_id .
1,2,3,4 - have one link_id, but not all of them have flag equal to 1.
8,9 - the same situation...
5,6,7 - fit the condition.

You can at least give a direction in the text ... I think I’ll make a request myself ... even blunted over such a simple situation ...

Answer 1

To search for the link_id themselves

select link_id from tablename
group by link_id
having count(*) = sum(flag  = 1)

Or

select id from links
where exists(select null from tablename where flag  = 1 and link_id = links.id) -- возможно это условие вам по задаче не нужно
and not exists(select null from tablename where flag != 1 and link_id = links.id)

You can try to read the lines themselves through

select ...
from tablename t
where t.flag = 1
and not exists(select null from tablename sq where sq.link_id = t.link_id and sq.flag != 1)

Which of the options is more effective depends critically on the distribution of data. With a large number of different link_ids with a small number of lines per link_id and a small expected number of matching lines for the task, it can be more efficient

select ... from tablename t where t.link_id in (
select q.link_id from tablename q
group by q.link_id
having count(*) = sum(flag  = 1)
)

With a large number of rows for each link_id and a small number of different link_ids, it may turn out to be more reasonable to similarly first get the link_id that satisfies the condition and already get data from them.
The third wait request will respond better to the case of a small number of flag = 1 rows in the table.
The fastest option for reading, however, is still not to process this filter on the fly, but somehow materialize it and store it pre-calculated.