0

i have 2 tables who includes thes sames colums but the differentes data.

  
   TOOLS                   DATE
-----------       ----------------------

A10MA17           2021-09-25 05:14:16.000
x10UA50           2021-09-25 05:06:50.000
LU0NV03           2021-09-25 05:04:49.000
NU0MAK8           2021-09-25 05:02:22.000
252WA17           2021-09-25 05:15:12.000

I would like to select the data which is in table (A) but which is not in table (B) at a later date, that is to say the program must select a data in the tables (A) and go see if it does not exist in the tables B at a later date.

i use this request but I don't know how to add the filter on the date

SELECT TOOLS FROM A WHERE A NOT IN (SELECT TOOLS FROM B)

thank's for help

Amenay Khial
  • 35
  • 4
  • They query isn't going to work anyway, `A` is a table, so you can't check to see it the *entire* table isn't equal to a value of `TOOLS` in another table. I would however, suggest using a `NOT EXISTS` that has a correlated sub query. – Thom A Oct 21 '21 at 10:47

1 Answers1

2

As I mentioned in the comment, I would suggest using a NOT EXISTS with a correlated subquery:

SELECT A.Tools
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.B
                  WHERE B.Tools = A.Tools
                    AND B.[Date] > A.[Date]);
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • 1
    @SMor aliasing a table called `A` as `A` would be completely pointless; in fact if you were writing something like a `VIEW` SQL Server would likely remove the alias declaration.. As for about the schema names, **both** tables are schema defined, so I have no idea what you are going on about there... `FROM dbo.A` and `FROM dbo.B`. – Thom A Oct 21 '21 at 11:05
  • 2
    @SMor what are you talking about? – Aaron Bertrand Oct 21 '21 at 11:11
  • At a best guess, @AaronBertrand , they want me to schema define the column names instead, however, such syntax is due to be deprecated, so doing so is not a good idea. – Thom A Oct 21 '21 at 11:13
  • Especially when they have an [answer](https://stackoverflow.com/a/69474785/2029983) from a couple of weeks ago with no aliasing nor schema names, @AaronBertrand . ;) – Thom A Oct 21 '21 at 11:22