compare data between two tables and populate a flag column in 1st table with ‘Y’if exist else ‘N”

I have two tables, if data exist in 1st table then populate flag column with ‘Y’ if does not populate with ‘N’, I am comparing only 3 columns, one is number, 2nd is name and 3rd column is datetime. However my busines rule case statment always returns ‘Y”

Table 1 (staging table, type1)
column1: number, data (123, 456,756)
column2: name, date('Mike', 'Dray','John')
column3: datetime. data('2018-12-03 14:00:52.000','2018-12-03 14:00:52.000','2018-12-03 14:00:52.000')

Table2 (landing table, type2)
column1 number, data (123, 456,756, 890)
column2: name, date('Mike', 'Dray','John','Chris')
column3: datetime. data('2018-12-03 14:00:52.000','2018-12-03 14:00:52.000','2018-12-03 14:00:52.000','2018-09-20 10:31:39.000')
column4: flagcolumn, data('Y','Y','Y','N')

so last column 4 in table 2 should populate to y/n based if data is in table 1 or not. I have wrote query as like:

   WITH CDE AS (
   SELECT T1.number,T1.name,T1.Bdatetime,       
   FROM dbo.db.table T1)
   ,CDE1 AS (
   SELECT  CDE.*,BUS_RULE_valid = (
                            select case 
                                    when EXISTS  (
                                    SELECT number, name, datetime
                                    FROM dbo.db.table T1
                            WHERE number IN                                       
                                    (SELECT number
                                    FROM  dbo.db.table2)
                            AND name 
                                    (SELECT  name FROM  dbo.db.t2)
                            AND datetime IN
                                    (SELECT datetime FROM  dbo.db.t2))
                                                  THEN 'Y'
                                                  ELSE 'N'
                                   END)
   FROM CDE
   )
   SELECT * FROM CDE1

Answer

You’re checking if the three values individually exist in the other table, which is likely almost always the case. Looks like you need to check for the combined value, which would look something like the query below.

I took the libery of removing the with part, since that didn’t seem necessary here. But you could keep it as well, the solutions would be the same (apart from the table alias used).

SELECT 
  T1.number_col,
  T1.name_col,
  T1.datetime_col,
  CASE WHEN EXISTS (
    SELECT 'x' FROM dbo.db.table2 T2
    WHERE
      t2.number_col = T1.number_col AND
      t2.name_col = T1.name_col AND
      t2.datetime_col = T1.datetime_col)
  THEN 'Y' 
  ELSE 'N'
  END AS BUS_RULE_valid
FROM dbo.db.table1 T1