I have a table with several fields and my goal is to bring only the id that appear only once and whose fields 3 and 4 are not null (any of them)
The query you make is the following
SELECT id
FROM tabla
WHERE campo3 IS NOT NULL
OR campo 4 IS NOT NULL
GROUP BY id
HAVING count(id) = 1
But it brings me a list of id where I try and appear more than once.
What is happening is that you are first filtering the values of campo3 and campo4 so that they are not null, and then you are seeing the id 's that are not repeated.
What you should do is the following:
SELECT t1.*
FROM tabla t1
INNER JOIN (SELECT id
FROM tabla
GROUP BY id
HAVING COUNT(ID) = 1 ) t2
ON t1.id = t2.id
WHERE t1.campo3 IS NOT NULL
OR t1.campo4 IS NOT NULL;
Or another way:
SELECT id
FROM tabla
GROUP BY id
HAVING COUNT(ID) = 1
AND (MIN(campo3) IS NOT NULL OR MIN(campo4) IS NOT NULL);
Thank you all for the help!
What I did was divide the query.
The idea is that first look which ones comply with the condition that one of those fields is null, and then on those it is fixed if they appear only once in the table.
SELECT id
FROM tabla
WHERE (
campo3 IS NOT NULL
OR campo 4 IS NOT NULL
)
AND id IN (
SELECT id
FROM tabla
GROUP BY id
HAVING count(id) = 1
)
This way it only brings me the records whose fields 3 or 4 are not null and which in turn have a id that appears only once in the table.
I hope it works if someone has a similar doubt.