I need to merge two recordsets with different columns in a single query. My recordsets are queries, but for the purposes of showing the problem, I'm representing them here as tables. Here is the setup in Access:

What I need is a datasheet with values for Data_1 and Data_2 when they exist, based on the values of ID1 and ID2:

I'm trying to do this by doing a left join from ID1 to ID1_1 and to ID1_2. This gives me the correct output for ID1 values of 1, 2, and 3. But I have not been able to get the right output of the two rows for ID1 = 4.

For example, the SQL:

SELECT ID1, nz(ID2_1, ID2_2) AS ID2, Data_1, Data_2
FROM (ID1 LEFT JOIN Table_1 ON ID1.ID1 = Table_1.ID1_1) 
          LEFT JOIN Table_2 ON ID1.ID1 = Table_2.ID1_2
WHERE Data_1 Is Not Null OR Data_2 Is Not Null;

gives me two rows for ID1 = 4, but the second one is wrong:

and the SQL:

SELECT ID1, nz(ID2_1, ID2_2) AS ID2, Data_1, Data_2
FROM (ID1 LEFT JOIN Table_1 ON ID1.ID1 = Table_1.ID1_1) 
          LEFT JOIN Table_2 ON ID1.ID1 = Table_2.ID1_2
WHERE ID2_1 is not null and ID2_2 is null or
      ID2_1 is null and ID2_2 is not null or
      ID2_1 = ID2_2;

gives me only one row for ID1 = 4:

What am I doing wrong? What do I need to do to get the required output?

CodePudding user response：

Consider:

SELECT Table_2.ID1_2, Table_2.ID2_2, Table_1.Data_1, Table_2.Data_2
FROM Table_2 LEFT JOIN Table_1 ON (Table_2.ID2_2 = Table_1.ID2_1) AND (Table_2.ID1_2 = Table_1.ID1_1)
UNION
SELECT Table_1.ID1_1, Table_1.ID2_1, Table_1.Data_1, Table_2.Data_2
FROM Table_1 LEFT JOIN Table_2 ON (Table_1.ID2_1 = Table_2.ID2_2) AND (Table_1.ID1_1 = Table_2.ID1_2);