SQL syntax
This is the example I want to follow:
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Erm ... no comments.yeah ok fair point. (I'm still laughing!)
Nevertheless! Not ALL users are.
ASKER
running that SQL...
ASKER
ah delete .Rxa01 I think.
ASKER
ah yes, that's a requirement, different field names, occasionally matching data, but different other fields content, I'll upload my test file
ODBCFILE.xlsx
ASKER
am I needing an equal in there? e.g. Rxa01 = AWS04 somewhere to make the match?
Not sure to see what you are speaking about.
Self join queries return matching rows from both tables.
Left join queries return all rows from the left table and the matching rows the right one (vice versa for right join).
Self join queries return matching rows from both tables.
Left join queries return all rows from the left table and the matching rows the right one (vice versa for right join).
ASKER
yes so I also want the unmatching rows - like a FULL JOIN but my ODBC driver doesn't support it so this is a work-around that I THINK does a LEFT JOIN and then adds the NULLS. Trouble is I don't understand how the author has presented his syntax. the single letters followed by words, such as d.color have me foxed. No idea what he is saying so I have guessed. Hence my Question in the hope you recognise what it is supposed to be.
Failing that at least he has given me the clue of doing a left join followed by a null add.
edit so I tried this:
SELECT * FROM AWSsheet;
FULL OUTER JOIN RXAsheet;
ON AWSsheet.AWS04 = RXAsheet.RXA01
and my ODBC driver said "not supported". Apparently it's based on SQ Lite (or some such).
Anthony
Failing that at least he has given me the clue of doing a left join followed by a null add.
edit so I tried this:
SELECT * FROM AWSsheet;
FULL OUTER JOIN RXAsheet;
ON AWSsheet.AWS04 = RXAsheet.RXA01
and my ODBC driver said "not supported". Apparently it's based on SQ Lite (or some such).
Anthony
ASKER
THIS seems to work:
SELECT * FROM AWSsheet
LEFT JOIN RXAsheet;
ON AWSsheet.AWS04 = RXAsheet.RXA01
SELECT * FROM AWSsheet
LEFT JOIN RXAsheet
ON AWSsheet.AWS04 = RXAsheet.RXA01
WHERE Rxa01 IS NULL;
SELECT * FROM AWSsheet
LEFT JOIN RXAsheet;
ON AWSsheet.AWS04 = RXAsheet.RXA01
SELECT * FROM AWSsheet
LEFT JOIN RXAsheet
ON AWSsheet.AWS04 = RXAsheet.RXA01
WHERE Rxa01 IS NULL;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect. Exactly what I needed to know.Thank you.
Anthony
Anthony
ASKER
SELECT * FROM RXAsheet,
LEFT JOIN AWSsheet.AWS04 USING(AWS04)
UNION ALL
SELECT * FROM AWSsheet
LEFT JOIN Rxasheet.Rxa01 USING(Rxa01)
WHERE Rxa01 IS NULL;
so it's a LEFT JOIN with the NULLs then added in?