Find rows with column equal and other different

I need to extract all rows that have same column value (wihout duplicates) and the other column value with different value.

I have problems when I’m trying to get null values since the sentence “having count (distinct)” not include null values.

My example (https://dbfiddle.uk/?rdbms=postgres_9.4&fiddle=ae1db1de07f9a54171c5121b49112273) :

create table definition
(
   job varchar(256),
   bat varchar(256),
   descr varchar(256)
);

INSERT INTO definition (job,bat,descr)
VALUES
   ('JOBA','SCRIPTA','caracoles'),
   ('JOBA','SCRIPTA','navajas'),
   ('JOBB','SCRIPTB','navajas'),
   ('JOBA','SCRIPTC','casa'),
      ('JOBC','SCRIPTC','asombrado'),
            ('JOBC','SCRIPTC','casa'),
      ('JOBC','SCRIPTKKK','asombrado'),
      ('JOBC','SCRIPTCDSFDF','pepelu'),
      ('JOBD',NULL,'casa'),
      ('JOBD','DFDSFDSFDSF','caracoles'),
   ('JOBA','SCRIPTC','casa'),
   ('JOBA','SCRIPTC','casa'),
   ('JOBJ','SCRIPTC','casa'),
   ('JOBB','SCRIPTB','love');

SELECT DISTINCT d.*
FROM definition d
JOIN
(
SELECT job
FROM definition
GROUP BY job
HAVING COUNT(DISTINCT(bat)) > 1
) x
ON d.job = x.job

It gives me:

job     bat
JOBA    SCRIPTA
JOBA    SCRIPTC
JOBC    SCRIPTC
JOBC    SCRIPTCDSFDF
JOBC    SCRIPTKKK
Warning: Null value is eliminated by an aggregate or other SET operation.

I need also

JOBD,NULL
JOBD,DFDSFDSFDSF

Answer

Since you are using postgres the syntax is slightly different

SELECT DISTINCT d.job,d.bat
FROM definition d
JOIN
(
SELECT job
FROM definition
GROUP BY job
HAVING COUNT(DISTINCT(case when bat is null then '' else bat end)) > 1
) x
ON d.job = x.job
order by 1

https://dbfiddle.uk/?rdbms=postgres_9.4&fiddle=27b4cf8e44d7a088331f70ba2b519644

Advertisement

Answer