What is the Microsoft Query Syntax for Subqueries?

Question

I am trying to do a simple subquery join in Microsoft Query, but I cannot figure out the syntax. I also cannot find any documentation for the syntax.

How would I write the following query in Microsoft Query?

SELECT *
FROM (
    SELECT Col1, Col2
    FROM `C:\Book1.xlsx`.`Sheet1$`
) AS a
JOIN (
    SELECT Col1, Col3
    FROM `C:\Book1.xlsx`.`Sheet1$`
) AS b
ON a.Col1 = b.Col1

Is there official documentation for Microsoft Query?

Thanks!

Answer 1

Follow Excel the Query Wizard to create/edit queries.

You can access it from Excel menu: Data->Import External Data->Import Data

As well you can check this link http://www.exceluser.com/explore/msquery1_1.htm

Answer 2

You may wish to look at: http://office.microsoft.com/en-us/excel/HA100996641033.aspx

Answer 3

SELECT * 
    FROM (SELECT ID, Company FROM (`Sheet1$`)) AS a 
    INNER JOIN 
    (SELECT ID, Name FROM `Sheet1$`) AS b 
    ON a.ID = b.ID;

That worked for me. It looks like the only difference is INNER JOIN vs. JOIN. My fields are ID, Company, Name in that order for Col1, Col2, Col3.