CODEWITHSUNDEEP
sql
MelodicVoid
MelodicVoid

Reputation: 1

Find rows where columns are the same but exclude where another column is equal to X

I have a table with the following Columns active, timeUpdated, firstName, lastName, Property

I need to display the full rows for where the property is duplicate and active is set to 1.

So far I have found a statement to display rows for a duplicate column but I can not figure out where to place a WHERE statement to only display the active rows

 select * from [Table] where property in (
    select property  from [table]
    group by property having count(*) > 1
)
Order by property DESC

Additionally I need to search for duplicate first names and last names that are active to ensure they are not active in multiple properties.

I appreciate any assistance with this and apologize if this was answered previous. I was unable to locate it. If it makes any difference I am using Microsoft SQL Server Management Studio

As requested sample data would be like (Active, DateUpdated, First, Last, propertyID)

1, 2018-09-17, John, Doe, 1408

1, 2018-10-20, Emily, Smith 1408

0, 2018-11-15, Greg Jones 1408

1, 2018-12-01, Richard, Smith 1209

I would want it to display the results for John Doe and Emily since they are both active and share a property ID

For the "Additional" it would be like

1, 2018-09-17, John, Doe, 1408

1, 2018-10-20, Emily, Smith 1408

0, 2018-11-15, Greg Jones 1408

1, 2018-12-01, Richard, Smith 1209

1, 2018-10-17, John, Doe, 1103

0, 2018-3-17, John, Doe, 1001

I would want it to return both active John Doe

Upvotes: 0

Views: 43

Answers (2)

Jitendra Gupta
Jitendra Gupta

Reputation: 824

CREATE TABLE Test (
    FirstName varchar(50),
    LastName varchar(50),
    Property varchar(50),
    Active int
);


INSERT INTO Test(FirstName, LastName, Property, Active)
VALUES
('Jitendra', 'Gupta', 'MyProperty', 1),
('Jitendra', 'Gupta', 'MyProperty', 1),
('Jay', 'Kumar', 'YourProperty', 0),
('Vinod', 'Singh', 'YourProperty', 0),
('Amit', 'Sharma', 'HisProperty', 1),
('Manoj', 'Yadav', 'HerProperty', 1),
('Pankaj', 'Gupta', 'OurProperty', 1);


WITH T1 (Property, DuplicateCount)
AS
(
    SELECT  Property,
            ROW_NUMBER() OVER(PARTITION BY Property ORDER BY Property) AS DuplicateCount
    FROM    Test 
    WHERE   Active = 1
)
SELECT  * 
FROM    Test 
WHERE   Property IN (SELECT Property FROM T1 WHERE DuplicateCount > 1)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I need to display the full rows for where the property is duplicate and active is set to 1.

You can use exists:

select t.*
from t
where exists (select 1
              from t t2
              where t2.property = t.property and t.active = 1
             );

Upvotes: 0

Related Questions