Flagging Results in SQL

Question

I have a table of addresses which I would like to flag the rows which match the towns in another table. Please can you advise if this is possible and the code required.

Example Tables:

Data Table

 Fname Surname Town        Postcode   Flag
 Alan  Jones   Colchester  CO1 2RT
 Steve Smith   Lincoln     LN23 7TY
 Jeff  Lilly   Swindon     SN1 6LK
 Sarah Thomas  Lincoln     LN56 9TT

Town Name Table

TownNames    
Lincoln
Swindon

So in the example above Steve Smith, Jeff Lilly and Sarah Thomas should be returned with an "X" in the flag column.

score 3 · Answer 1 · edited Jun 20 '16 at 14:37

3

Try this:

SELECT a.Fname, a.Surname, a.Town, a.Postcode, 
       CASE t.TownNames IS NOT NULL THEN 'X' ELSE '' END AS Flag
FROM Address AS a LEFT JOIN Towns AS t ON a.Town = t.TownNames;

edited Jun 20 '16 at 14:37

Vahid Farahmandian

6,081
7
42
62

answered Jun 20 '16 at 11:35

vercelli

4,717
2
13
15

score 1 · Accepted Answer · edited May 23 '17 at 11:58

If you want to do this directly in the table then you can create a persisted computed column that will do this for you. You'll have to make a function to do the calculation you're after (either a CASE or IF EXISTS).

The advantage of a computed column is that it will keep itself up to date, you don't have to run a regular update to make sure that it's in there.

Function;

CREATE FUNCTION dbo.TownCheck (@Town VARCHAR(1))
RETURNS VARCHAR(1) 
AS BEGIN
    DECLARE @TownExists VARCHAR(1)

    SELECT @TownExists = MAX('X') FROM dbo.TownNames WHERE TownName = @Town

    RETURN @TownExists
END

Let's make your sample data;

CREATE TABLE DataTable (Fname varchar(5), Surname varchar(6), Town varchar(10), Postcode varchar(8), Flag varchar(1))
INSERT INTO DataTable (Fname, Surname, Town, Postcode)
VALUES
('Alan','Jones','Colchester','CO1 2RT')
,('Steve','Smith','Lincoln','LN23 7TY')
,('Jeff','Lilly','Swindon','SN1 6LK')
,('Sarah','Thomas','Lincoln','LN56 9TT')

And the second table;

CREATE TABLE TownNames (TownName varchar(10))
INSERT INTO TownNames
VALUES 
('Lincoln')
,('Swindon')

You can't alter a column to make it calculated so you have to drop it and then recreate it (I've assumed you already have this table, if you don't then do this at the point of creation of the table);

ALTER TABLE dbo.DataTable
DROP COLUMN Flag

ALTER TABLE dbo.DataTable
ADD Flag AS dbo.TownCheck(Town)

Now your DataTable will look like this;

Fname   Surname Town        Postcode    Flag
Alan    Jones   Colchester  CO1 2RT     NULL
Steve   Smith   Lincoln     LN23 7TY    X
Jeff    Lilly   Swindon     SN1 6LK     X
Sarah   Thomas  Lincoln     LN56 9TT    X

Edit:

This will work if the TownNames table is updated. If you insert the missing town;

INSERT INTO TownNames
VALUES ('Colchester')

You get this result;

Fname   Surname Town        Postcode    Flag
Alan    Jones   Colchester  CO1 2RT     X
Steve   Smith   Lincoln     LN23 7TY    X
Jeff    Lilly   Swindon     SN1 6LK     X
Sarah   Thomas  Lincoln     LN56 9TT    X

Flagging Results in SQL

2 Answers2