I have the following dataset in SQL:

I want to find all the individuals for whom none of their Town_2012 values appear in the Town_2017 column, so the output should be

How do I find whether or not any of the values in Town_2012 for each individual appear in Town_2017? I've tried GROUP BY, HAVING, and WHERE but have not quite nailed the code.

CodePudding user response：

Here's one solution, where you first find the name of people you don't want with an INTERSECT query, and then you select everyone that isn't found in that query result.

select *
from table1
where individuals not in (
    select individuals from (
        select individuals, town_2012
        from table1
        intersect
        select individuals, town_2017
        from table1
    )z
  );

Output:

CodePudding user response：

Sory for untested code....im driving!

Try this:

SELECT ind.* 
FROM INDIVIDUALS ind
WHERE ind.Town_2012 NOT IN (
    SELECT DISTINCT Town_2017 FROM INDIVIDUALS)
)

CodePudding user response：

Set up your table:

CREATE TABLE dbo.INDIVIDUALS
    (
    NAME varchar(10) NULL,
    TOWN_2012 varchar(20) NULL,
    TOWN_2017 varchar(20) NULL
    )  ON [PRIMARY]
GO

Set up your data set:

INSERT INTO dbo.INDIVIDUALS VALUES ('John', 'London', 'Manchester');
INSERT INTO dbo.INDIVIDUALS VALUES ('John', 'London', 'London');
INSERT INTO dbo.INDIVIDUALS VALUES ('Steve', 'Manchester', 'Leeds');
INSERT INTO dbo.INDIVIDUALS VALUES ('Steve', 'Bristol', 'Birmingham');

First write the subquery to select all the users that have matches and then use NOT IN to exclude those users. Here is the query:

-- Subselect identifies name of individuals that have matches in 2017 and 2012
-- Then use NOT IN to exclude those people
SELECT NAME, TOWN_2012, TOWN_2017 FROM INDIVIDUALS
WHERE NAME NOT IN (
    SELECT DISTINCT NAME FROM dbo.INDIVIDUALS I2017
    WHERE I2017.TOWN_2017 IN (SELECT I2012.TOWN_2012 FROM dbo.INDIVIDUALS I2012 WHERE I2012.NAME = I2017.NAME)
);