SQL Query-EXCEL_OLEDB

[NagaYaparla]

Hi All, As I mentioned in Subject, i am connecting Excel as Database my query is "SELECT [Sheet1$].[ID], [Sheet1$].[name], [Sheet1$].[Email], [Sheet2$].[ID], [Sheet2$].[name], [Sheet2$].[Email] from [Sheet1$] inner join [Sheet2$] on [Sheet1$].[ID] = FORMAT([Sheet2$].[ID],'000000000') WHERE [Sheet1$].[name][Sheet2$].[name] AND [Shee1$].[Email][Sheet2$].[Email]"  i don't have that much coding experience, in this query i am excepting output like if ID matches in both worksheets it will check name and Email if name matches and email is not matching i want to display output as id and name column is empty and email which is not matching both sheets sheet1 email, sheet2 email. it's looks like ID       name    Email 102                  [email protected];[email protected] Any one has any idea on this please comment below. Thanks in advance..  

[John__Carter]

An inner join will return only the data matching the criteria. Think of a Venn diagram with 2 intersecting circles - the inner join is represented by the intersecting area. However there are other types of join, eg 'left outer join' that may be worth some research to see if they will provide what you want.

[BenKirimlidis]

Hi yaparla, This is your query below ----------------------------------- SELECT [Sheet1$].[ID] , [Sheet1$].[name] , [Sheet1$].[Email] , [Sheet2$].[ID] , [Sheet2$].[name] , [Sheet2$].[Email] from [Sheet1$] inner join [Sheet2$] on [Sheet1$].[ID] = FORMAT([Sheet2$].[ID],'000000000') WHERE [Sheet1$].[name][Sheet2$].[name] AND [Shee1$].[Email][Sheet2$].[Email] ----------------------------------- This query returns a relation where the ID matches on both sheets, and the 'name' columns on both sheets DO NOT MATCH and the 'email' on both sheets DO NOT MATCH.  You said you wanted to see where the 'name' DO MATCH and the 'email' DO NOT MATCH. Try changing the WHERE clause to: WHERE [Sheet1$].[name] = [Sheet2$].[name] AND [Shee1$].[Email][Sheet2$].[Email] That should give you your desired output.  But John is right, definitely explore different types of join.  You can work wonders with just INNER JOINS and LEFT OUTER JOINS. Kind regards, Ben

[NagaYaparla]

@Ben, John Thank you so much for your time.. i tried above query it will give not matching data in both columns (name, Email), but am looking output looks like Sheet1:  ID                    name            Email                                  Sheet2:  ID             name                 Email         00000000123           test          [email protected]                                123             test                     [email protected]         00000000345           abc            [email protected]                               345             bcd                     [email protected]         00000000678           xyz             [email protected]                               789             xyz                     [email protected] Output: ID                                  name                         Email 00000000123                                                [email protected];[email protected] 00000000345                abc;bcd                                         00000000678                  xyz                           [email protected]   Please suggest me how can i write code based on above output Thanks in advance..