troubleshooting Question

SQL syntax

Avatar of Anthony Mellor
Anthony MellorFlag for United Kingdom of Great Britain and Northern Ireland asked on
SQLMicrosoft ExcelMicrosoft Office
12 Comments2 Solutions27 ViewsLast Modified:
This is the example I want to follow:

Emulating SQLite full outer join
The following statement emulates the FULL OUTER JOIN clause in SQLite:

SELECT d.type,
         d.color,
         c.type,
         c.color
FROM dogs d
LEFT JOIN cats c USING(color)
UNION ALL
SELECT d.type,
         d.color,
         c.type,
         c.color
FROM cats c
LEFT JOIN dogs d USING(color)
WHERE d.color IS NULL;

and this is my attempt so far, where my tables are in Excel and RXAsheet and AWSsheet are tables in a sheet, treated by MS Query as named tables as such.
Fields in each table respectively are
RXA01 to RXA03 and
AWS04 to AWS10
The "common" fields are RXA01 to AWS04 but not all match so I am attempting a FULL OUTER JOIN, desiring all records to be acquired. My Mac ODBC driver does not support FULL OUTER JOINS so this is meant to be a work-around. However the d.type is outside my thin knowledge of the subject.

SELECT RXAsheet,
         Rxa01,
         Rxa02,
         Rxa03
FROM Rxasheet
LEFT JOIN AWSsheet.AWS04 USING(AWS04)
UNION ALL
SELECT RXAsheet,
     Rxa01,
         Rxa02,
         Rxa03
FROM AWSsheet
LEFT JOIN Rxasheet.Rxa01 USING(Rxa01)
WHERE Rxa01 IS NULL;

originally from here: https://www.sqlitetutorial.net/sqlite-full-outer-join/

bit miffed here as it looks like MS Query has been trashed in favour of Power Query and that looks like /rantmodeon  it is another of these block-access-to-mere-users "more functional" efforts that increase the gap between "developers" and users. Note to MS: Users are not stupid, but do not make their living as developers. grrr! /rantmodeoff

Anthony
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers 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 2 Answers 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