Bdtjtk

I'm trying to compare some data from different multiple databases, as I have illustrate my current case, I have there databases, database 1 is the main, and time to time database 2 and database 3 are updated from database 1. I have some difficulties to get the final result which return the data from database 1 and two columns column show the availability in database 2 as Yes or No, and the same with second extra column that will indicate the data availability on the database 3 with Yes or NO.

SELECT * 

FROM (

Select  ID      as  db1_ID,

    First_name  as  db1_First_name,

    Last_name   as  db1_Last_name,

    Email       as  db1_Email,

    Password    as  db1_Password,

    Request_Id  as  db1_Request_Id,

    User_Id     as  db1_User_Id,

    Request_name    as  db1_Request_name

from User 

inner join User_request 

on User_request.User_Id = user.ID

)   AS DB1_VIEW



LEFT OUTER JOIN 



(

Select  ID      as  db2_ID,

    First_name  as  db2_First_name,

    Last_name   as  db2_Last_name,

    Email       as  db2_Email,

    Password    as  db2_Password,

    Request_Id  as  db2_Request_Id,

    User_Id     as  db2_User_Id,

    Request_name    as  db2_Request_name

from User 

inner join User_request 

on User_request.User_Id = user.ID

)   AS DB2_VIEW



ON  db2_ID  = db1_ID 



LEFT OUTER JOIN 



(

Select  ID      as  db3_ID,

    First_name  as  db3_First_name,

    Last_name   as  db3_Last_name,

    Email       as  db3_Email,

    Password    as  db3_Password,

    Request_Id  as  db3_Request_Id,

    User_Id     as  db3_User_Id,

    Request_name    as  db3_Request_name

from User 

inner join User_request 

on User_request.User_Id = user.ID

)   AS DB3_VIEW



ON  db3_ID   = db1_ID

           ID   First_name  Last_name   Email   Password    Request_Id  User_Id Request_name

            1   Oliver  Jake    OJake@domain.com    123         1            1   Request1

            2   Mathew  Harry   MHarry@domain.com   123         1            2   Request1

            3   Jacob   Reece   JReece@domain.com   123         1            3 

Request1

            4   Charlie Damian  CDamian@domain.com  123         1            4   Request1

Use this as your first select statement:

SELECT  DB1_VIEW.*

   ,CASE WHEN DB2_VIEW.db2_ID IS NOT NULL THEN 'Y' ELSE 'N' END AS Available_db2

,CASE WHEN DB3_VIEW.db3_ID IS NOT NULL THEN 'Y' ELSE 'N' END AS Available_db3

You can remove all the details apart from the ID fields in the db2_view and db3_view subqueries.

This might not need CTE's or sub-queries.
A few joins might do it.

SELECT

Usr1.ID AS db1_User_Id,

Usr1.First_name AS db1_First_name,

Usr1.Last_name AS db1_Last_name,

Usr1.Email AS db1_Email,

Usr1.Password AS db1_Password,

MAX(UsrReq1.Request_Id) AS db1_Request_Id,

MAX(UsrReq1.Request_name) AS db1_Request_name,

CASE WHEN COUNT(UsrReq2.User_Id) > 0 THEN 'Y' ELSE 'N' END AS Available_Db2,

CASE WHEN COUNT(UsrReq3.User_Id) > 0 THEN 'Y' ELSE 'N' END AS Available_Db3

FROM [Database1].[User] AS Usr1

LEFT JOIN [Database1].[User_request] AS UsrReq1 ON UsrReq1.User_Id = Usr1.ID

LEFT JOIN [Database2].[User] AS Usr2 ON Usr2.ID = Usr1.ID

LEFT JOIN [Database2].[User_request] AS UsrReq2 ON UsrReq2.User_Id = Usr2.ID

LEFT JOIN [Database3].[User] AS Usr3 ON Usr3.ID = Usr1.ID

LEFT JOIN [Database3].[User_request] AS UsrReq3 ON UsrReq3.User_Id = Usr3.ID

GROUP BY

Usr1.ID,

Usr1.First_name,

Usr1.Last_name,

Usr1.Email,

Usr1.Password;

You can use the below query before execute you should use replace [SourceDB] to your source database and [TargertDB] to your target database. Insert the table name into #mdtables to include for comparison.

USE [SourceDB] 



IF Object_id('tempdb..#mdTables') IS NOT NULL 

  DROP TABLE #mdtables; 



CREATE TABLE #mdtables 

  ( 

     id         INT IDENTITY(1, 1) NOT NULL, 

     schemaname NVARCHAR(128), 

     tablename  NVARCHAR(128) 

  ); 



INSERT INTO #mdtables 

            (schemaname, 

             tablename) 

VALUES      ('dbo', 

             'user'); 



DECLARE @mdTableLim   INT =0, 

        @mdTableRowId INT =0 



SELECT @mdTableLim = Count(*) 

FROM   #mdtables; 



SET @mdTableRowId = 1; 



WHILE @mdTableRowId <= @mdTableLim 

  BEGIN 

      DECLARE @SDBName   VARCHAR(50) = '[SourceDB]', 

              @TDBName   VARCHAR(50) = '[TargertDB]', 

              @tableName VARCHAR(100) = '' 

      DECLARE @WhereF        VARCHAR(max) ='', 

              @joincondition VARCHAR(max) ='', 

              @or            VARCHAR(10) ='', 

              @select        VARCHAR(max) = '', 

              @comma         VARCHAR(1)='', 

              @query         VARCHAR(max) ='', 

              @and           VARCHAR(5)='', 

              @where1        VARCHAR(1000) ='', 

              @wOR           VARCHAR(5)='' 



      SELECT @tableName = tablename 

      FROM   #mdtables 

      WHERE  id = @mdTableRowId; 



      SELECT @joincondition += Isnull(@and + ( CASE 

                                                 WHEN cu.column_name IS NULL 

                                               THEN 

                                                 NULL 

                                                 ELSE ' src.[' + cu.column_name 

                                                      + 

                                                      '] = ' + 

                                                      'trgt.[' 

                                                      + c.column_name + ']' 

                                               END ), ''), 

             @WhereF += Isnull (@or + ( CASE 

                                          WHEN cu.column_name IS NOT NULL THEN 

                                          NULL 

                                          ELSE Isnull ( '  src.[' + 

                                               TC.column_name 

                                               + 

                                               ']  ', 

                                               ' isnull( src.[' + 

                                               C.column_name + 

                                               '],1)  ' ) 

                                               + Isnull( '<> trgt.[' + 

                                               TC.column_name 

                                               + ']', 

                                               ' = isnull (src.[' 

                                               + 

                                               C.column_name + '],1)  ') 

                                        END ), ''), 

             @or = ( CASE 

                       WHEN cu.column_name IS NOT NULL THEN '' 

                       ELSE ' OR ' 

                     END ), 

             @and = ( CASE 

                        WHEN cu.column_name IS NULL THEN '' 

                        ELSE ' AND ' 

                      END ), 

             @select += @comma + ' src.[' + c.column_name + ']  ' 

                        + Isnull (' , trgt.[' + TC.column_name + ']', ''), 

             @comma = ',', 

             @where1 += Isnull(( @wOR + ( CASE 

                                            WHEN cu.column_name IS NULL THEN 

                                            NULL 

                                            ELSE ' trgt.[' + cu.column_name + 

                                                 '] is null  ' 

                                          END ) ), ''), 

             @wOR = ( CASE 

                        WHEN cu.column_name IS NULL THEN '' 

                        ELSE ' OR ' 

                      END ) 

      FROM   information_schema.columns C 

             LEFT JOIN information_schema.key_column_usage CU 

                    ON C.column_name = cu.column_name 

                       AND constraint_name LIKE 'PK_%' 

                       AND c.table_name = cu.table_name 

             LEFT JOIN [TargertDB].information_schema.columns TC 

                    ON C.column_name = TC.column_name 

                       AND c.table_name = TC.table_name 

      WHERE  c.table_name = @tableName 

             --AND columnproperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 0    

             AND c.column_name NOT IN ( 'LST_CHG_TMS', 'LST_CHG_TMS', 

                                        'LST_CHG_USR_ID' 

                                        , 

                                        'LST_CHG_USR_ID' ) 

             AND c.data_type NOT IN ( 'image' ) 

      ORDER  BY cu.column_name 



      SET @query = 'select ' + @select + ' from ' + @SDBName + '.dbo.' 

                   + @tableName + ' as src left join  ' + @TDBName 

                   + '.dbo.' + @tableName + ' as trgt on ' 

                   + @joincondition + ' where (' + @where1 + ')' 

                   + Isnull ('and '+ NULLIF (@WhereF, ''), '') 



      DECLARE @qu1 VARCHAR(max) = 

              ' declare @cnt int =0        select @cnt =count (1) from ' 

        + @SDBName + '.dbo.' + @tableName 

        + ' as src left join  ' + @TDBName + '.dbo.' 

        + @tableName + ' as trgt on ' + @joincondition 

        + ' where (' + @where1 + ')' 

        + Isnull (' OR '+ NULLIF (@WhereF, ''), '') 

        + '    if (@cnt>0)       begin     select ''' 

        + @tableName + ''' as [  ],@cnt         ' +-- @query + '    end    ' 



      BEGIN try 

          EXECUTE ( @qu1) 

      END try 



      BEGIN catch 

          PRINT @qu1; 

      END catch 



      SET @mdTableRowId = @mdTableRowId + 1 

  END