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                  test1@gmail.com;test2@gmail.com 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          test@gmail.com                                123             test                     test1@gmail.com         00000000345           abc            abc@gmail.com                               345             bcd                     abc@gmail.com         00000000678           xyz             xyz@gmail.com                               789             xyz                     xyz@gmail.com Output: ID                                  name                         Email 00000000123                                                test@gmail.com;test1@gmail.com 00000000345                abc;bcd                                         00000000678                  xyz                           xyz@gmail.com   Please suggest me how can i write code based on above output Thanks in advance..