SQL Join syntax - which one and how?
I only rarely dip in to SQL via MS Excel's MS Query.
So I saw the question linked to my post here:
https://www.experts-exchange.com/questions/29214473/MS-Excel-for-Mac-v16-47-1-Merge-two-sheets-with-all-fields-into-one-based-on-a-matched-field.html#a43281754
I thought " that's easy, SQL does that". Instead I find myself in a world of pain and frustration because I m sure there was a time I could do this easy as falling off a log. I have spent hours "refreshing" my memory, but every time the syntax details catch me out and what I expect to see , based on my reading, simply doesn't happen.
I solved the question, to my own satisfaction given it lacks details, using a bit of pasting and a pivot table. However, that leaves my SQL thoughts as a fail.
How exactly do you write a simple SQL query (so no aliasses or whatever) to get the same result as shown in this pivot table file?
ODBCFILE03.xlsx
I even had great help shown here:
https://www.experts-exchange.com/questions/29214795/SQL-syntax.html#a43282023
but to no avail, still I failed.
It's one thing to think I understand the principles in play, but quite another to be able to write the syntax so I get predictable results. I suppose I am seeing why Power Query and its "point and click" interface I guess like Pivot Tables will have its attractions when it eventually arrives on the Mac.
I remember I do like SQL satisfaction when I can get it to work, but there is a disconnect between all the docs I read and actually achieving this.
My ODBC driver does not support full joins (edit NOR RIGHT JOINS), hence the workaround shown in my question here:
https://www.experts-exchange.com/questions/29214795/SQL-syntax.html#a43281927
I was up until 4am this morning failing to do this. I have TRIED!
Example:
SELECT * FROM RXAsheet,
LEFT JOIN AWSsheet USING(AWS04)
UNION ALL
SELECT * FROM AWSsheet
LEFT JOIN Rxasheet USING(Rxa01)
WHERE Rxa01 IS NULL;
to join these two tables:
File is here:
ODBCFILE.xlsx
these two sheets:
Save me please?
Anthony
So I saw the question linked to my post here:
https://www.experts-exchange.com/questions/29214473/MS-Excel-for-Mac-v16-47-1-Merge-two-sheets-with-all-fields-into-one-based-on-a-matched-field.html#a43281754
I thought " that's easy, SQL does that". Instead I find myself in a world of pain and frustration because I m sure there was a time I could do this easy as falling off a log. I have spent hours "refreshing" my memory, but every time the syntax details catch me out and what I expect to see , based on my reading, simply doesn't happen.
I solved the question, to my own satisfaction given it lacks details, using a bit of pasting and a pivot table. However, that leaves my SQL thoughts as a fail.
How exactly do you write a simple SQL query (so no aliasses or whatever) to get the same result as shown in this pivot table file?
ODBCFILE03.xlsx
I even had great help shown here:
https://www.experts-exchange.com/questions/29214795/SQL-syntax.html#a43282023
but to no avail, still I failed.
It's one thing to think I understand the principles in play, but quite another to be able to write the syntax so I get predictable results. I suppose I am seeing why Power Query and its "point and click" interface I guess like Pivot Tables will have its attractions when it eventually arrives on the Mac.
I remember I do like SQL satisfaction when I can get it to work, but there is a disconnect between all the docs I read and actually achieving this.
My ODBC driver does not support full joins (edit NOR RIGHT JOINS), hence the workaround shown in my question here:
https://www.experts-exchange.com/questions/29214795/SQL-syntax.html#a43281927
I was up until 4am this morning failing to do this. I have TRIED!
Example:
SELECT * FROM RXAsheet,
LEFT JOIN AWSsheet USING(AWS04)
UNION ALL
SELECT * FROM AWSsheet
LEFT JOIN Rxasheet USING(Rxa01)
WHERE Rxa01 IS NULL;
to join these two tables:
File is here:ODBCFILE.xlsx
these two sheets:
Save me please?
Anthony
You should tell what is your expected output from the query.
The basic issue in your query is different column order in the two parts of UNION.
I've tested following query in SSMS and it should work the same way if I connect to your Excel sheet via ODBC driver from any client:
The same result (with different row order) is possible to achieve when you use WHERE and UNION ALL instead of simple UNION:
The basic issue in your query is different column order in the two parts of UNION.
I've tested following query in SSMS and it should work the same way if I connect to your Excel sheet via ODBC driver from any client:
The same result (with different row order) is possible to achieve when you use WHERE and UNION ALL instead of simple UNION:
SELECT COALESCE(AWSsheet.AWS04, RXAsheet.Rxa01) AS KeyValue, AWSsheet.*, RXAsheet.*
FROM @AWSsheet AWSsheet
LEFT JOIN @RXAsheet RXAsheet ON RXAsheet.Rxa01 = AWSsheet.AWS04
UNION ALL
SELECT COALESCE(AWSsheet.AWS04, RXAsheet.Rxa01) AS KeyValue, AWSsheet.*, RXAsheet.*
FROM @AWSsheet AWSsheet
RIGHT JOIN @RXAsheet RXAsheet ON RXAsheet.Rxa01 = AWSsheet.AWS04
WHERE AWSsheet.AWS04 IS NULL
This query was tested with ODBC driver from Visual FoxPro:
SELECT AWSsheet.AWS04 AS KeyValue, AWSsheet.*, RXAsheet.*
FROM [AWSsheet$] AWSsheet
LEFT JOIN [RXAsheet$] RXAsheet ON RXAsheet.Rxa01 = AWSsheet.AWS04
UNION ALL
SELECT RXAsheet.Rxa01 AS KeyValue, AWSsheet.*, RXAsheet.*
FROM [AWSsheet$] AWSsheet
RIGHT JOIN [RXAsheet$] RXAsheet ON RXAsheet.Rxa01 = AWSsheet.AWS04
WHERE AWSsheet.AWS04 IS NULLASKER
You have two excel sheets that you are trying to combine?
Both tables combined as one, nothing missing.
I guess no duplicates means drop the ALL from Union All.
No server involved, just one single Excel file with two sheets in it to combine. Very simple use/r.
I don't know what that means. "Control parameters"? I would guess not needed, but just guessing.
I would wish to work on the resulting output in Excel.
Been a while: I'll say, I am using an original 1994 MS Query V1.0 printed manual out of my "museum".
The only relationship between these two tables is RXA01 and AWS04 contain SOME matching entries. No matched indices or foreign indexes. Copy and paste would be quicker, but I'm trying to learn/remember how to do this, and in the future there will be matched unique index columns, just not now. That's assuming PowerQuery doesn't render all this obsolete when it arrives on Mac.
I think my commercial ODBC for Mac driver is (very) limited in what it can do, for example not data changes (Is that INSERT?) or writing back to the tables with updates. FULL JOIN is not supported, nor is RIGHT JOIN, hence this problem which is to create a workaround based on two LEFT JOINs as I understand it.
Here is what I am thinking should be my output:
I used a pivot table to do that in this file which also shows the two tables:
ODBCFILE03.xlsx
I need more time to read your replies, but this reply is in the hope of clarifying my question.
Anthony
Yes. Same Excel file. No activity outside file, not an external query.Yes Mac 365 Sub Beta channel. Sorry should have said.
You should tell what is your expected output from the query.See below image and file.
Both tables combined as one, nothing missing.
I guess no duplicates means drop the ALL from Union All.
Or the query you generate against the SQL server and get a single sheet in the excel?
No server involved, just one single Excel file with two sheets in it to combine. Very simple use/r.
Much depends on whether you need control parameters that are available within excel to alter the data in the worksheet.
I don't know what that means. "Control parameters"? I would guess not needed, but just guessing.
I would wish to work on the resulting output in Excel.
Been a while, but using the excel SQL query you have to make sure to add the two tables, then tie the correct relationship....
Been a while: I'll say, I am using an original 1994 MS Query V1.0 printed manual out of my "museum".
The only relationship between these two tables is RXA01 and AWS04 contain SOME matching entries. No matched indices or foreign indexes. Copy and paste would be quicker, but I'm trying to learn/remember how to do this, and in the future there will be matched unique index columns, just not now. That's assuming PowerQuery doesn't render all this obsolete when it arrives on Mac.
I've tested following query in SSMS and it should work the same way if I connect to your Excel sheet via ODBC driver from any client:
I think my commercial ODBC for Mac driver is (very) limited in what it can do, for example not data changes (Is that INSERT?) or writing back to the tables with updates. FULL JOIN is not supported, nor is RIGHT JOIN, hence this problem which is to create a workaround based on two LEFT JOINs as I understand it.
Here is what I am thinking should be my output:
I used a pivot table to do that in this file which also shows the two tables:
ODBCFILE03.xlsx
I need more time to read your replies, but this reply is in the hope of clarifying my question.
Anthony
All the INSERTs in my answer were used to populate the data on SQL Server. The important part is the query itself.
The query from your ODBCFILE.xlsx file in my second answer produces following results:
The query from your ODBCFILE.xlsx file in my second answer produces following results:
If the RIGHT JOIN is not supported by your ODBC (which I cannot believe) then you may update the query this way:
SELECT AWSsheet.AWS04 AS KeyValue, AWSsheet.*, RXAsheet.* FROM [AWSsheet$] AWSsheet
LEFT JOIN [RXAsheet$] RXAsheet ON RXAsheet.Rxa01 = AWSsheet.AWS04
UNION ALL
SELECT RXAsheet.Rxa01 AS KeyValue, AWSsheet.*, RXAsheet.* FROM [RXAsheet$] RXAsheet
LEFT JOIN [AWSsheet$] AWSsheet ON RXAsheet.Rxa01 = AWSsheet.AWS04
WHERE AWSsheet.AWS04 IS NULL
ASKER
Sadly I get this:
from this:
I gather it is because the driver is based on sqlight.
I've edited my op to add right to full join not being supported.
Anthony
from this:
SELECT AWSsheet.AWS04 AS KeyValue, AWSsheet.*, RXAsheet.* FROM [AWSsheet$] AWSsheet LEFT JOIN [RXAsheet$] RXAsheet ON RXAsheet.Rxa01 = AWSsheet.AWS04 UNION ALL SELECT RXAsheet.Rxa01 AS KeyValue, AWSsheet.*, RXAsheet.* FROM [AWSsheet$] AWSsheet RIGHT JOIN [RXAsheet$] RXAsheet ON RXAsheet.Rxa01 = AWSsheet.AWS04 WHERE AWSsheet.AWS04 IS NULLSELECT AWSsheet.AWS04 AS KeyValue, AWSsheet.*, RXAsheet.*
FROM [AWSsheet$] AWSsheet
LEFT JOIN [RXAsheet$] RXAsheet ON RXAsheet.Rxa01 = AWSsheet.AWS04
UNION ALL
SELECT RXAsheet.Rxa01 AS KeyValue, AWSsheet.*, RXAsheet.*
FROM [AWSsheet$] AWSsheet
RIGHT JOIN [RXAsheet$] RXAsheet ON RXAsheet.Rxa01 = AWSsheet.AWS04
WHERE AWSsheet.AWS04 IS NULL
I gather it is because the driver is based on sqlight.
I've edited my op to add right to full join not being supported.
Anthony
ASKER
SELECT AWSsheet.AWS04 AS KeyValue, AWSsheet.*, RXAsheet.* FROM [AWSsheet$] AWSsheet
LEFT JOIN [RXAsheet$] RXAsheet ON RXAsheet.Rxa01 = AWSsheet.AWS04
UNION ALL
SELECT RXAsheet.Rxa01 AS KeyValue, AWSsheet.*, RXAsheet.* FROM [RXAsheet$] RXAsheet
LEFT JOIN [AWSsheet$] AWSsheet ON RXAsheet.Rxa01 = AWSsheet.AWS04
WHERE AWSsheet.AWS04 IS NULLSELECT AWSsheet.AWS04 AS KeyValue, AWSsheet.*, RXAsheet.* FROM [AWSsheet$] AWSsheet
yes I couldn't believe it either, having just paid money for it.
ODBCFILE.xlsx
assuming I am actually reading the correct file
Anthony
having trouble pasting that code in it looks like it is duplicating
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Fox Pro.. I remember that from the previous century.
Yes, even when not supported FoxPro can still do the job as the desktop prototyping application. And thousands of applications are still running in production environment because to port them to a newer environment is rather expensive task...
ASKER
Sounds very nice.
You have two excel sheets that you are trying to combine?
Or the query you generate against the SQL server and get a single sheet in the excel?
If it is a single query that you want the output displayed on a single, I usually create the query via ssms.
Then within excel, connection, SQL tab I paste the query.
Much depends on whether you need control parameters that are available within excel to alter the data in the worksheet.
Been a while, but using the excel SQL query you have to make sure to add the two tables, then tie the correct relationship....
On a Mac based on prior Images?