Sql query find all rows where value is null but another row wih same value not null (Mariadb)-CodePudding

id_product|pn|ean13|supplier|
-----------------------------
1 |1F46G| FGH45642346|1|
2 |8BBBB| null |1|
3 |1F46G| null |2|
4 |1F46G| FGH45642346 |3|

Hello I have table structure like this (just more rows).

I want select all rows where ean13 is null but exists some row where is same PN but ean13 is not null.

SELECT id_product,pn
                FROM product
                WHERE pn !='' AND (ean13 is null OR ean13 = '')
                GROUP BY pn
                HAVING count(pn)>1

This Select partly working but shows rows where not exists next row with ean13 which is not null

I tried use function exists but the duration is really long time.

CodePudding user response：

A query based on your precise description select all rows where ean13 is null but exists some row where is same PN but ean13 is not null would look like the following. There's nothing that would indicate any aggregation.

I don't know what you mean by I tried use function exists since you have not included this.

select id_product, pn
from product p
where ean13 is null
  and exists (
    select * from product p2 where p2.pn = p.pn and p2.ean is not null
);

CodePudding user response：

You can rewrite the query as below to get the desired output. Write a subquery to identify pn having ean13 as not null.

SELECT id_product,pn
from product
WHERE pn !='' AND (ean13 is null OR ean13 = '')
and pn in(
select pn from product where not (ean13 is null OR ean13 = '')
);

DB Fiddle: Result