MS Access - alternative to performing a "full join" for columns with same name

Question

I have this problem using access: I am using the RIGHT + LEFT outer joins to overcome the fact that ACCESS does not support the FULL JOIN.

SELECT *
FROM T1 RIGHT OUTER JOIN T2
ON T1.xxx = T2.xxx 
UNION 
SELECT *
FROM T1 LEFT OUTER JOIN T2
ON T1.xxx = T2.xxx

on these tables:

T1:
ID1 | xxx | fieldA
a     1       X
b     2       Y
c     3       Z

T2:
ID2 | xxx | fieldB
d      2       K
e      3       J
f      4       H

AS a result I obtain a table with this structure

T1.xxx | T2.xxx | fieldA | fieldB | ID1 | ID2
   1                 X               a
   2        2        Y       K       b     d
   3        3        Z       J       c     e
            4                H             f

xxx is not primary key but has the same name and numerical type (integer)

I saw from many other places that this should work by collapsing the two tables! Here it does not (the elements on the same rows, when non blank, are of course the same)

MY EXPECTATION

FINAL TABLE:
    xxx |  ID1 | ID2 |fieldA | fieldB
     1      a            X
     2      b     d      Y       K
     3      c     e      Z       J
     4            f              H

Could you please rephrase your question to be more clear? What is the problem? — Vadim, Jul 25 '13 at 14:47
Search for `[sql] full outer join` in the search box; there are a large number of questions about full outer join for systems that do not support it natively, including: [Full outer join in MySQL](http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql) and [Full outer join with SQLite](http://stackoverflow.com/questions/1923259/full-outer-join-with-sqlite). The second has an interesting tweak compared to the first. — Jonathan Leffler, Jul 25 '13 at 14:56
It would also be helpful if you showed simple schemas for T1 and T2, with sample data, the result you're getting, and the result you expected. It may be that you're getting the correct result but your expectation is wrong. — Jonathan Leffler, Jul 25 '13 at 14:57
Thanks for adding the tables and results — it makes the issue clear in a way that I'd not have guessed from your explanation. You are looking for a NATURAL FULL OUTER JOIN, not a FULL OUTER JOIN. You'd have to `COALESCE(T1.xxx, T2.xxx) AS xxx` and list the other columns explicitly to get the result you want. (See also [BNF Grammar for ISO/IEC 9075-2:2003](http://savage.net.au/SQL/sql-2003-2.bnf.html#joined%20table).) It would be nice if your actual result table were fully filled in like your expected result table is. — Jonathan Leffler, Jul 25 '13 at 15:16
Answers to a question should be posted as answers, not as part of the question (even if it is your own question.) The members of the StackOverflow community are building a collection of questions and answers. — spencer7593, Jul 25 '13 at 15:52
You are encouraged to post an answer in response to your own question, if you found an answer to your question; this can be shared with the community. — spencer7593, Jul 25 '13 at 18:16
thanks @JonathanLeffler but the coalescence is not recognized in access. anyway I was posting the solution in the answer field but my early account privileges sucks, now it cannot be seen not even on the question, I will try to fix tomorrow. =) — MC-8, Jul 25 '13 at 22:54
Does Access have `NVL` or something similar? What about `CASE`? If worst comes to worst, then `CASE WHEN T1.xxx IS NOT NULL THEN T1.xxx ELSE T2.xxx END`. Otherwise, it might be time to abandon Access, or abandon your NATURAL FULL OUTER JOIN. — Jonathan Leffler, Jul 25 '13 at 23:32
Urgh...It appears that NZ is the nearest you get; it appears that NVL and CASE are not supported. Hmmm...I think you should do the coalescing in your application code, or the VB code, rather than trying to do it in the SQL (SELECT) per se. 'Tis tempting to say "get a real DBMS", but there are undoubtedly reasons why you are using Access. — Jonathan Leffler, Jul 25 '13 at 23:43
@JonathanLeffler I guess you are right, doing in VB should be much faster, these days I'll learn more VB coding and maybe move that big query somewhere. Thanks you all your support anyway — MC-8, Jul 26 '13 at 11:51

score 1 · Answer 1 · answered Jul 25 '13 at 14:47

1

It seems that there are different set of column values for both of these 2 tables, you could be having t1.xxx and t2.xxx which have the same values, but other columns dont, the union in this case wouldn't combine these 2 records

Try something like

SELECT T1.xxx
FROM T1 RIGHT OUTER JOIN T2
ON T1.xxx = T2.xxx 
UNION 
SELECT T2.xxx
FROM T1 LEFT OUTER JOIN T2
ON T1.xxx = T2.xxx

Something like this should give you all the xxx values from table 1 and table 2, ignoring duplicate values for xxx.

answered Jul 25 '13 at 14:47

Akash

4,956
11
42
70

Akash your solution provides a column with only the shared xxx between the two tables, so in my examples it gives as output only two rows with the xxx 2 and 3 – MC-8 Jul 25 '13 at 15:14
I just solved using a similar solution, which I am posting now in the end of the question – MC-8 Jul 25 '13 at 15:38

spencer7593 · Answer 2 · 2013-07-25T15:24:17.770

The most likely explanation for the behavior you observe is that the rows being returned that are not identical, they are not exact duplicates.

The UNION operator will remove duplicate rows, but it doesn't do anything to "collapse tables" other than that.

To get the specified result set (in the updated question), here's one SQL pattern that would return the result. (I don't know if Access supports this, but this would work in MySQL, SQL Server, Oracle, etc.)

 SELECT i.xxx
      , v1.ID AS ID1
      , v2.ID AS ID2   
      , v1.fieldA AS fieldA   
      , v2.fieldB AS fieldB   
   FROM (
          SELECT t1.xxx AS xxx
            FROM T1 t1
           UNION
          SELECT t2.xxx
            FROM T2 t2
        ) i
   LEFT
   JOIN T1 v1
     ON v1.xxx = i.xxx
   LEFT
   JOIN T2 v2
     ON v2.xxx = i.xxx

(NOTE: if xxx is not guaranteed to be unique in each table, then this query could generate duplicate rows.)

score 0 · Answer 3 · answered Jul 25 '13 at 22:56

MY SOLUTION (absolutely not elegant):

SELECT [QUERY1].XX, ID1, ID2, fieldA, fieldB
FROM (T2 RIGHT JOIN [QUERY1] ON T2.xxx = [QUERY1].XX) LEFT JOIN T1 ON [QUERY1].XX = T1.XX;

where QUERY1 is the following:

(SELECT CInt(NZ(T1.xxx,T2.xxx)) as XX
    FROM T1 RIGHT OUTER JOIN T2
    ON T1.xxx = T2.xxx
    UNION 
    SELECT CInt(NZ(T1.xxx,T2.xxx)) as XX 
    FROM T1 LEFT OUTER JOIN T2
    ON T1.xxx = T2.xxx)
    UNION (SELECT CInt(NZ(T2.xxx,T1.xxx))  as xx1
    FROM T1 RIGHT OUTER JOIN T2
    ON T1.xxx = T2.xxx
    UNION 
    SELECT CInt(NZ(T2.xxx,T1.xxx)) as xx1
    FROM T1 LEFT OUTER JOIN T2
    ON T1.xxx = T2.xxx);

notice that xx1 is not used, and not even shown in the table

The Hill Boy · Answer 4 · 2013-10-17T09:10:22.437

TRY THIS ONE: I have tried and get both result sets.

DECLARE @T1 TABLE
(
ID1 VARCHAR(2) ,XXX INT, FIELDA VARCHAR(2)
)
DECLARE @T2 TABLE
(
ID2 VARCHAR(2) ,XXX INT, FIELDB VARCHAR(2)
)


INSERT INTO @T1 VALUES 
('a',     1,       'X'),
('b',     2,       'Y'),
('c',     3,       'Z')

INSERT INTO @T2 VALUES 
('d',     2,       'k'),
('e',     3,       'j'),
('f',     4,       'h')

SELECT
    ISNULL(CONVERT(VARCHAR(1),T1.xxx),' ') AS [T1.xxx] , 
    ISNULL(CONVERT(VARCHAR(1),T2.xxx),'') AS [T2.xxx] , 
    ISNULL(fieldA,'') AS [fieldA], 
    ISNULL(fieldB,'') AS [fieldB] , 
    ISNULL(ID1,'') AS [ID1] , 
    ISNULL(ID2,'') AS [ID2]
FROM 
(
    SELECT XXX as XXX1 FROM @T1
    UNION
    SELECT XXX as XXX1  FROM @T2
)T LEFT OUTER JOIN 
@T1 T1 ON T.XXX1 = T1.XXX 
LEFT OUTER JOIN
@T2 T2 ON T.XXX1 = T2.XXX

----------------------RESULT------------------

T1.xxx  T2.xxx  ID1 ID2 fieldA  fieldB
1       a       X   
2   2   b   d   Y   k
3   3   c   e   Z   j
    4       f       h

SELECT
    ISNULL(CONVERT(VARCHAR(1),T.xxx1),' ') AS [xxx] ,
    ISNULL(ID1,'') AS [ID1] ,   
    ISNULL(ID2,'') AS [ID2],
    ISNULL(fieldA,'') AS [fieldA], 
    ISNULL(fieldB,'') AS [fieldB] 

FROM 
(
    SELECT XXX as XXX1 FROM @T1
    UNION
    SELECT XXX as XXX1  FROM @T2
)T LEFT OUTER JOIN 
@T1 T1 ON T.XXX1 = T1.XXX 
LEFT OUTER JOIN
@T2 T2 ON T.XXX1 = T2.XXX

----------------------RESULT------------------

xxx ID1 ID2 fieldA  fieldB
1   a       X   
2   b   d   Y   k
3   c   e   Z   j
4       f       h

MS Access - alternative to performing a "full join" for columns with same name

4 Answers4