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 ALLSELECT * FROM AWSsheetLEFT JOIN Rxasheet USING(Rxa01)WHERE Rxa01 IS NULL;
to join these two tables:
File is here:ODBCFILE.xlsx
these two sheets:
Save me please?
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?