1

I am trying to write a SQL statement that first gets only the distinct values of column 1, I then want to compare this with the same table but remove any rows that have a value in column 2

Screenshot

For example, the value 10142 in FieldID when I write a select that doesn't include 10142 it only removes the 1 row but also the subsequent ID column to have no rows.

So in the screenshot, I should only see all results for only ID 634 as 633 has the FieldID value 10142.

I tried initially getting a distinct ID value into a temporary table and then filtering in another select where the FieldID was not equal to 10142 but still not seeing the correct result.

This is my query:

SELECT DISTINCT id
INTO #TEMP
FROM tbl_WorkItemCustomLatest
ORDER BY ID ASC

SELECT a.*
FROM #TEMP
INNER JOIN dbo.tbl_WorkItemCustomLatest AS A ON #TEMP.id = A.id
WHERE A.FieldID != 10142
ORDER BY A.ID ASC

Any help is much appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
rdbmsNoob
  • 121
  • 1
  • 12
  • What is your expected output? Also, convert images into the text just copy and paste what you want to include in your question in text format. Images can not be copied for editing purposes. – Suraj Kumar Apr 08 '20 at 14:09
  • Hi Suraj, I am trying to get an output that only shows the rows where the ID column doesnt have any values that exist in its FieldID column. For example the screenshot I would only want the rows for the ID 634 as that has no values that match FieldID 10142 if that makes sense? Regards Ian – rdbmsNoob Apr 08 '20 at 14:13

1 Answers1

0

With NOT EXISTS:

select t.* from tbl_WorkItemCustomLatest t
where not exists (
  select 1 from tbl_WorkItemCustomLatest
  where id = t.id and FieldId = 10142
)

or with NOT IN:

select * from tbl_WorkItemCustomLatest 
where id not in (select id from tbl_WorkItemCustomLatest where FieldId = 10142)
forpas
  • 160,666
  • 10
  • 38
  • 76