There are two tables, one-to-many relationship. Suppose that the table t1 is the main one.

Accordingly, in the table t2 there can be [0; N] entries for the row from the table t1. It is clear that in the table t2 there will be an idT1 column.

Imagine that we have a status column in m2, valid values: 1 - 5.

I need to select those lines of p1 for which there are lines from p2, and all of them have status not included in any range, for example: 1,2,3.

I tried to do this:

SELECT t1.* FROM Table1 t1 JOIN Table2 t2 ON t1.id = t2.t1Id WHERE t2.status NOT IN (1, 2, 3) GROUP BY t2.t1Id ORDER BY t1.id DESC;

But it does not work out correctly, because even those lines from m1 are selected, where there is at least one line from m2 with status 4 or 5.

And what is the correct sql query?

    2 answers 2

     SELECT t1.* FROM Table1 t1 JOIN Table2 t2 ON t1.id = t2.t1Id GROUP BY t2.t1Id having sum(case when status in(1, 2, 3) then 1 else 0 end)=0 ORDER BY t1.id DESC;
    • swear on group by will not it? or will display 1 entry. oracle just be cursed. - Denis
    • The Oracle will swear, but same Mysql. ^ -) - msi
    • And why is grouping by t2.t1Id field, when it is possible (and logically - and necessary) to group by t1.id? And - for MySQL, you can get rid of one conversion by writing simply sum (status in (1, 2, 3)) = 0 . - Akina
    • I just edited the query in question. But since the grouping is performed after the connection, I think that it is all the same for what to group. But it is better to compare plans. - msi

    If I understand your data correctly, SELECT distinct t1 should help. *