Hi Vshah555

try this code

CREATE TABLE #tbl1
    (
      ID INT NULL,
      NAME VARCHAR(10) NULL ,
      LicenseNo VARCHAR(10) NULL,
      NationalLicenseNumber VARCHAR(10) NULL
    )
 INSERT INTO #tbl1
 VALUES ( 1, 'ABC,XYZ', NULL, NULL )
 INSERT INTO #tbl1
 VALUES ( 1, 'CDS,ABD', NULL, NULL )
 CREATE TABLE #tbl2
    (
      ID INT ,
      NAME VARCHAR(10) ,
      LicenseNo VARCHAR(10) ,
      NationalLicenseNumber VARCHAR(10)
    )
 INSERT INTO #tbl2
 VALUES ( 1, 'XYZ ABC', 1234567, 976633 )
 INSERT INTO #tbl2
 VALUES ( 1, 'ABD CDS', 4324223, 304242 )

 SELECT *
 FROM   ( SELECT    a.*
          FROM      #tbl1 AS a
          UNION ALL
          SELECT    b.*
          FROM      #tbl2 AS b
        ) AS tmp
 WHERE  tmp.id IS NOT NULL
        AND tmp.NAME IS NOT NULL
        AND tmp.LicenseNo IS NOT NULL
        AND tmp.NationalLicenseNumber IS NOT NULL

 DROP TABLE #tbl1
 DROP TABLE #tbl2

Hi Mitesh, Thanks for the prompt response. I tried the above code and it works but the result-set i need is total of 8 columns i.e. ID NAME LICENSENO NationalLicenseNumber ID NAME LICENSENO NationalLicenseNumber 1 XYZ ABC 1234567 976633 1 ABC,XYZ NULL NULL Appreciate your help. Thanks

Try this one may help.

CREATE TABLE #tbl1
    (
      ID INT NULL,
      NAME VARCHAR(10) NULL ,
      LicenseNo VARCHAR(10) NULL,
      NationalLicenseNumber VARCHAR(10) NULL
    )
 INSERT INTO #tbl1
 VALUES ( 1, 'ABC,XYZ', NULL, NULL )
 INSERT INTO #tbl1
 VALUES ( 1, 'CDS,ABD', NULL, NULL )
 CREATE TABLE #tbl2
    (
      ID INT ,
      NAME VARCHAR(10) ,
      LicenseNo VARCHAR(10) ,
      NationalLicenseNumber VARCHAR(10)
    )
 INSERT INTO #tbl2
 VALUES ( 1, 'XYZ ABC', 1234567, 976633 )
 INSERT INTO #tbl2
 VALUES ( 1, 'ABD CDS', 4324223, 304242 )
;WITH cte AS 
(
 SELECT   TOP 1 b.*
          FROM   #tbl1 AS b 
 WHERE  
        b.NAME IS NULL
        OR b.LicenseNo IS NULL
        OR b.NationalLicenseNumber IS NULL
)
SELECT TOP 1 a.*,cte.* FROM #tbl2 a INNER JOIN 
    cte ON a.ID = cte.ID  
WHERE  
    a.NAME IS NOT NULL
        AND a.LicenseNo IS NOT NULL
        AND a.NationalLicenseNumber IS NOT NULL


 DROP TABLE #tbl1
 DROP TABLE #tbl2

Hi Mitesh, The above script is giving me top 1, i am trying to get the result as: ID NAME LICENSENO NationalLicenseNumber ID NAME LICENSENO NationalLicenseNumber 1 XYZ ABC 1234567 976633 1 ABC,XYZ NULL NULL 1 ABD CDS 4324223 304242 1 CDS,ABD NULL NULL When i remove top 1 from the above query, it gives me duplicate rows for same Name from left table.

Is there any unique Id in table1 and table2.How can link table1 with table2?

Please send table with three or four entry and required output. please send data in proper format. To format data select the text to be formatted as code sample and either press CTRL+K or click on the toolbar button code sample [the one that shows 101010] and the formatting will be done.

Try this may help

CREATE TABLE #tbl1
    (
      ID INT NULL,
      NAME VARCHAR(10) NULL ,
      LicenseNo VARCHAR(10) NULL,
      NationalLicenseNumber VARCHAR(10) NULL
    )
 INSERT INTO #tbl1
 VALUES ( 1, 'ABC,XYZ', NULL, NULL )
 INSERT INTO #tbl1
 VALUES ( 1, 'CDS,ABD', NULL, NULL )
 CREATE TABLE #tbl2
    (
      ID INT ,
      NAME VARCHAR(10) ,
      LicenseNo VARCHAR(10) ,
      NationalLicenseNumber VARCHAR(10)
    )

 INSERT INTO #tbl2
 VALUES ( 1, 'XYZ ABC', 1234567, 976633 )
 INSERT INTO #tbl2
 VALUES ( 1, 'ABD CDS', 4324223, 304242 )

;WITH ctetbl1 AS (
 SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY id)AS Rno, b.*
          FROM   #tbl1 AS b  WHERE   b.NAME IS NULL
        OR b.LicenseNo IS NULL  OR b.NationalLicenseNumber IS NULL
),ctetbl2 AS
(
 SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY id)AS Rno, a.*
          FROM   #tbl2 AS a  WHERE a.NAME IS NOT NULL
        AND a.LicenseNo IS NOT NULL
        AND a.NationalLicenseNumber IS NOT NULL
        AND a.id IN (SELECT id FROM ctetbl1)
)
SELECT 
ctetbl2c.id,ctetbl2c.NAME,ctetbl2c.LicenseNo,ctetbl2c.NationalLicenseNumber,
ctetbl1.id,ctetbl1.NAME,ctetbl1.LicenseNo,ctetbl1.NationalLicenseNumber
 FROM ctetbl1
  OUTER APPLY
  (SELECT * FROM ctetbl2 WHERE  
    ctetbl1.Rno=ctetbl2.Rno)AS ctetbl2c

 DROP TABLE #tbl1
 DROP TABLE #tbl2

Hi Mitesh, I tried with ROW_NUMBER as well but i am not getting correct result. The only option i have is matching with Name as if i do by ID or the other fields, it gives me the result based on 1 to Many Relationship. No unique key is set as the design is based on the LicenseNo and StateLicenseNumber. The hierarchy is as follow one ID can have multiple Names along with LicenseNo or StateLicenseNumber from 2 tables. We came across scenarios where name, licenseno and statelicensenumber does not match and that's OK but i want to find out and display the result set where LicenseNo and StateLicenseNumber exist in one table for a particular name but does not exist in other table. I am able to find records by joining 2 tables on name but i wanted to see if SQL Server allows you to find with other method. I guess, there is no other method. Thanks for all your help

Thanks for your sharing