Exclude rows where value in column not found in another row

• jaymezek 9886

  SSC Rookie

  Points: 27

  More actions

  July 16, 2014 at 5:47 pm

  #293495

  This is a followup to a previous question to a previous but in reverse of Find rows where value in column not found in another row

  Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...

  I would like to exclude any two rows where Type is equal to 'TypeA' and Key2 is Null that have a corresponding row in the table where Type is equal to 'TypeB' and Key2 is equal to Key1 from another row.

  So, given the data

  **KEY1** **Key2** **Type**

  1 NULL TypeA

  2 5 TypeA

  3 1 TypeB

  4 NULL TypeA

  5 NULL TypeB

  6 26 TypeC

  7 NULL TypeD

  8 NULL TypeD

  I would like to return all the rows except where Key=1 and Key=3 because those rows together meet the criteria of Type='TypeA'/Key2=NULL and does have a corresponding row with Type='TypeB'/Key1=Key2.

• josjonkmans

  SSC Veteran

  Points: 279

  More actions

  July 18, 2014 at 2:48 am

  #1730518

  You could do that with an apply:

  SELECT T1.[key1],T1.[key2],T1.[type]

  FROM tablename T1

  OUTER APPLY (SELECT TOP 1 T2.[key1]

  FROM tablename T2

  WHERE T1.[type] = T2.[type]

  AND T1.[key1] <> T2.[key1]) A

  WHERE A.[key1] IS NULL

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 18, 2014 at 3:58 am

  #1730531

  DROP TABLE #Temp

  CREATE TABLE #Temp (KEY1 INT, Key2 INT, [Type] VARCHAR(5))

  INSERT INTO #Temp VALUES

  (1,NULL, 'TypeA'),

  (2,5, 'TypeA'),

  (3,1, 'TypeB'),

  (4,NULL, 'TypeA'),

  (5,NULL, 'TypeB'),

  (6,26, 'TypeC'),

  (7,NULL, 'TypeD'),

  (8,NULL, 'TypeD')

  SELECT *

  FROM #Temp ot

  WHERE NOT EXISTS (

  SELECT 1

  FROM #Temp it

  WHERE (it.[Type] = 'TypeB'

  AND it.Key2 = ot.Key1

  AND ot.[Type] = 'TypeA'

  AND ot.Key2 IS NULL)

  OR (ot.[Type] = 'TypeB'

  AND ot.Key2 = it.Key1

  AND it.[Type] = 'TypeA'

  AND it.Key2 IS NULL)

  )

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden