Get SQL Server result using subquery

Question

I have a Members table like this:

PersonID    FirstName   Address      City      date
---------------------------------------------------------
    3       Rasanga     Skagen 21    South     2019-01-05

and a Persons table:

PersonID  FirstName   Address     City       date
-------------------------------------------------------
   3      Rasanga     Skagen 21   South      2019-01-06
   1      Tom B.      Skagen 21   Colombo    2018-01-07
   2      Tom B.      Skagen 21   Colombo    2019-01-05

I want to get Persons that do not exists in Members table using the FirstName column. For that I'm using this query:

SELECT * 
FROM Persons p
WHERE NOT EXISTS (SELECT * FROM Members m WHERE m.FirstName = p.FirstName)

When I execute above query I'm getting same FirstName and 2 records but my requirement is if there's 2 records for same name retrieve latest record using date column. Therefore above scenario it should be Tom B. with 2018-01-07 record. If both records have same date should retrieve 1 record from 2 records.

Can somebody explain how to do this?

Why are you using the firstname to join when you have a personid? You need more explanation on what you are doing. — Gordon Linoff
– Gordon Linoff, Commented May 10, 2020 at 13:08

Suraj Kumar · Accepted Answer · 2020-05-10 07:11:40Z

1

You can use the left join and checking the Members.PersonId is null.

create table Members(PersonID int
, FirstName varchar(20)
, Address varchar(50)
, City varchar(50)
, Dtdate date)

insert into Members values
(3, 'Rasanga', 'Skagen 21', 'South', '2019-01-05')

Create table Persons(PersonID int
, FirstName varchar(20)
, Address varchar(50)
, City varchar(50)
, Dtdate date)

insert into Persons values
(3, 'Rasanga', 'Skagen 21', 'South', '2019-01-06'),
(1, 'Tom B.', 'Skagen 21', 'Colombo', '2018-01-07'),
(2, 'Tom B.', 'Skagen 21', 'Colombo', '2019-01-05')

Select Persons.* from Persons
left join Members on Persons.PersonID = Members.PersonID
where Members.PersonId is null

Demo

Using the not exists you can check as shown below.

SELECT Persons.*
FROM   Persons
WHERE  NOT EXISTS (SELECT 1 
                   FROM  Members 
                   WHERE  Persons.PersonID = Members.PersonID)

Using the in operator

SELECT * FROM Persons
WHERE PersonID NOT IN (
 SELECT PersonID FROM Members
)

To get the unique records based on the first name and date you can use the following query using ROW_NUMBER() function.

;WITH cte
AS (
    SELECT Persons.*
       ,ROW_NUMBER() OVER (
        PARTITION BY Persons.FirstName ORDER BY Persons.Dtdate DESC
        ) AS RN
    FROM Persons
    LEFT JOIN Members ON Persons.PersonID = Members.PersonID
    WHERE Members.PersonId IS NULL )
SELECT *
FROM CTE
WHERE RN = 1

Output

PersonID    FirstName  Address     City     Dtdate     RN
----------------------------------------------------------
2           Tom B.     Skagen 21   Colombo  2019-01-05  1

edited May 10, 2020 at 7:11

answered May 10, 2020 at 6:35

Suraj Kumar

5,6748 gold badges24 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AbRe Over a year ago

Hi @Suraj Kumar thanks for your answer. But I'm still getting 2 records, my requirement is if there's 2 records for same name retrieve latest record using date column. If both records have same date should retrieve 1 record from 2 records.

Suraj Kumar Over a year ago

@RasangaAbeykoon Please check with the current edit using ROW_NUMBER() function.

Ilyes · Accepted Answer · 2020-05-10 09:11:28Z

0

You could use a window function as

SELECT T.PersonID,
       T.FirstName,
       T.Address,
       T.City,
       T.[Date]
FROM
(
  SELECT *, ROW_NUMBER() OVER(PARTITION BY FirstName ORDER BY [Date] DESC) RN
  FROM Persons
) T
WHERE NOT EXISTS
(
  SELECT 1
  FROM Members 
  WHERE FirstName = T.FirstName
) AND T.RN = 1;

Here is a db<>fiddle

answered May 10, 2020 at 9:11

Ilyes

15k5 gold badges31 silver badges61 bronze badges

Collectives™ on Stack Overflow

Get SQL Server result using subquery

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related