troubleshooting Question

SQL Join syntax - which one and how?

Avatar of Anthony Mellor
Anthony MellorFlag for United Kingdom of Great Britain and Northern Ireland asked on
SQLMicrosoft SQL ServerMicrosoft ExcelMicrosoft Office
12 Comments1 Solution61 ViewsLast Modified:
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



ASKER CERTIFIED SOLUTION
Pavel Celba
All in One

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros