Get SQL Server result using subquery

user_10289876 0
Viewed 56

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?

subquery sql-server sql
2 Answers

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
user_105325000

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

user_64266920
Related