MS Access Full Outer Join using 2 fields?

Question

I have 2 tables that I want to do a FULL OUTER JOIN on in MS Access.

Say I have Table A which looks like this:

A.ID    |  A.Value  | A.DATE
--------+-----------+----------
  1     |     30    | 05/2018
  1     |     28    | 06/2018
  1     |     26    | 07/2018
  2     |     250   | 04/2018
  2     |     252   | 05/2018
  2     |     240   | 06/2018

And Table B which looks like this:

B.ID    |  B.FCST   | B.OUTDATE
--------+-----------+-----------
  1     |     35    | 06/2018
  1     |     33    | 07/2018
  1     |     30    | 08/2018
  2     |     300   | 06/2018
  2     |     280   | 07/2018
  2     |     260   | 08/2018

And I need to perform Joins and Unions to achieve this:

 A.ID   |  A.Value  | A.DATE  |  B.FCST   | B.OUTDATE
--------+-----------+---------+-----------+------------
  1     |     30    | 05/2018 |     -     |     -
  1     |     28    | 06/2018 |     35    |   06/2018
  1     |     26    | 07/2018 |     33    |   07/2018
  1     |     -     |    -    |     30    |   08/2018
  2     |     250   | 04/2018 |     -     |     -
  2     |     252   | 05/2018 |     -     |     -
  2     |     240   | 06/2018 |     300   |   06/2018
  2     |     -     |    -    |     280   |   07/2018
  2     |     -     |    -    |     260   |   08/2018

So I need to do an Inner Join with A.ID = B.ID, AND A.DATE = B.OUTDATE, and then somehow get "earlier" data from Table.A to "Sit on top" of the inner joined data, and the "later" data from Table.B to do the opposite. This is my attempt so far:

Select A.ID, A.Value, A.DATE, B.FCST, B.OUTDATE
FROM Table.A JOIN Table.B ON A.ID = B.ID AND A.DATE = B.OUTDATE

UNION ALL

Select A.ID, A.Value, A.DATE, B.FCST, B.OUTDATE
FROM Table.A LEFT JOIN Table.B ON A.ID = B.ID;
WHERE B.ID IS NULL

UNION ALL

Select A.ID, A.Value, A.DATE, B.FCST, B.OUTDATE
FROM Table.A RIGHT JOIN Table.B ON A.ID = B.ID
WHERE A.ID IS NULL

ORDER BY A.ID ASC;

But I've missed the mark it appears. I'm getting duplicate lines, and it just looks like an Inner Join. I will gladly take any advice as to help get this right.

The joins should be comparable, but you only joined on the ID in the LEFT and RIGHT join, so it will produce matches irrespective of the dates. There are many matches for ID only, that's why you are getting unexpected / duplicate matches. Your own statement should work fine if you add the date columns to the join conditions. — C Perkins, Nov 28 '18 at 23:53
Full join on is inner join on rows union all unmatched left table rows extended by nulls union all unmatched right table rows extended by nulls. The first union is left join on. But you are not using the same ON every time. Also if nulls can be input then your "is null" doesn't necessarily give you unmatched rows. Always give a [mcve]. But doing MySQL full outer join is a faq. — philipxy, Nov 29 '18 at 05:01

Lee Mac · Accepted Answer · 2018-11-28T23:55:53.760

2

I would suggest a union of two left joins to give the same result as full outer, and finally with a touch of sorting to yield the desired ordering:

select c.* from
(
    select a.id, a.value, a.date, b.fcst, b.outdate
    from a left join b on a.id = b.id and a.date = b.outdate
    union
    select b.id, a.value, a.date, b.fcst, b.outdate
    from b left join a on a.id = b.id and a.date = b.outdate
) c
order by c.id, nz(c.date, c.outdate)

edited Nov 28 '18 at 23:55

answered Nov 28 '18 at 22:59

Lee Mac

15,615
6
32
80

1) Just exclude the `all` to automatically exclude duplicate rows, no need to use extra where clause. 2) There is no need to define a subquery. Just include an ORDER BY clause after the last union query: `ORDER BY id, [date], outdate`. 3) If `[date]` is null it will automatically revert to the next sorted column, so there is no need to call nz() which may not be a problem here, but really is not very efficient. – C Perkins Nov 28 '18 at 23:45
(1) I agree. (2) The subquery is necessary else the `ORDER BY` would only apply to the last union query. (3) Without `nz` nulls will be ordered first. – Lee Mac Nov 28 '18 at 23:54
No, an ORDER BY after a string of UNION queries applies to the entire union results (at least as long as you remove the source table qualifiers as I did in my example). The column names of the ORDER BY should match the columns of the first query, since technically the columns names (only the number and data types) must match in a UNION. (3) Agreed about nz() for date sort if that particular sort feature is required. – C Perkins Nov 28 '18 at 23:55
1

@CPerkins But without the table qualifiers, the `nz` would fail to operate and the sort order would be wrong. – Lee Mac Nov 28 '18 at 23:57
Thanks @LeeMac, I was thinking about this in too many pieces, I'm on the right track now and I'll be able to get the result I need. – BigDave_76 Nov 29 '18 at 16:06

MS Access Full Outer Join using 2 fields?

1 Answers1