Help me with find rows with column equal and other different

Transact-SQL
1

Hi,
I need to extract all rows that have same column value 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:

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

INSERT INTO definition (job,bat)
VALUES
   ('JOBA','SCRIPTA'),
   ('JOBA','SCRIPTA'),
   ('JOBB','SCRIPTB'),
   ('JOBA','SCRIPTC'),
      ('JOBC','SCRIPTC'),
            ('JOBC','SCRIPTC'),
      ('JOBC','SCRIPTKKK'),
      ('JOBC','SCRIPTCDSFDF'),
      ('JOBD',NULL),
      ('JOBD','DFDSFDSFDSF'),
   ('JOBA','SCRIPTC'),
   ('JOBA','SCRIPTC'),
   ('JOBB','SCRIPTB');

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:

|JOBA |SCRIPTA|
|JOBA |SCRIPTC|
|JOBC |SCRIPTC|
|JOBC |SCRIPTCDSFDF|
|JOBC |SCRIPTKKK|

I need:

JOBA,SCRIPTA
JOBA,SCRIPTC
JOBC,SCRIPTC
JOBC,SCRIPTCDSFDF
JOBC,SCRIPTKKK
JOBD,NULL
JOBD,DFDSFDSFDSF

Please any help? thanks

2

What happens if you did

SELECT DISTINCT d.*
FROM definition d

3

Not works.
The table have more columns. If I put distinct it shows me rows that have only one register. I don't need that. I need rows duplicates by a column and different in other column.
Thanks

4

Select distinct job,bat from table

1 Like
5

Change Join to Left join

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

6

...preceding_same_as_before...
(
SELECT job
FROM definition
GROUP BY job
HAVING COUNT(DISTINCT(ISNULL(bat, '~~~~'))) > 1 --<<--
) x
ON d.job = x.job