MS Access - an alternative to making a "full join" for columns with the same name

Question

I have this issue using access: I use external RIGHT + LEFT connections to overcome the fact that ACCESS does not support 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 

in 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 get a table with this structure

 T1.xxx | T2.xxx | fieldA | fieldB | ID1 | ID2 1 X a 2 2 YK bd 3 3 ZJ ce 4 H f 

xxx is not a primary key, but has the same name and numeric type (integer)

I have seen from many other places that this should work by folding two tables! This is not the case here (elements on the same lines, if they are not empty, of course)

MY WAITING

 FINAL TABLE: xxx | ID1 | ID2 |fieldA | fieldB 1 a X 2 bd YK 3 ce ZJ 4 f H 

Answer 1

It seems that there are different column values ​​for both of these two tables, you can have t1.xxx and t2.xxx that have the same values, but the other columns do not matter, union in this case won't join 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 the duplicate values ​​for xxx .

Answer 2

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

The UNION operator will delete duplicate rows, but it does nothing to “collapse tables” other than this.

---

To get a given set of results (in an updated question), here is one SQL template that will return the result. (I don't know if Access supports this, but it will 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 can generate duplicate rows.)

Answer 3

MY DECISION (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 as follows:

 (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); 

note that xx1 is not used or even displayed in the table

Answer 4

TRY THIS ONE: I tried to get both sets of results.

 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 bd Y k 3 3 ce Z j 4 fh 

---

 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 bd Y k 3 ce Z j 4 fh 

---