In Base Combine Two Tables with Different Columns But There Are Non-Duplicate Rows

[rmerriam]

I have two tables, p1 and p2. Each has a field ‘env’ which is the key to the table. The table p1 has fields env, 101, 102. The table p2 has fields env, 4015, 4016.

The output needs to have the fields env, 101, 102, 4015, 4016.

The trick is p1 may have values for env of 1, 2, 3, 5 and p2 may have 1, 2, 7. The output should have 1, 2, 3, 5, 7. The rows 1 and 2 should have the combined information from p1 and p2.

I actually have seen an answer but cannot now find it.

[PYS]

Hi

I hope I have understood the need:

(SELECT  "p1"."env" "p1-env", "p2"."env" "p2-env", "p1"."101", "p1"."102", "p2"."4015", "p2"."4016" FROM "p1"
RIGHT JOIN  "p2"
ON "p1"."env" = "p2"."env")
UNION
(SELECT "p1"."env" "p1-env", "p2"."env" "p2-env","p1"."101", "p1"."102", "p2"."4015", "p2"."4016" FROM "p1"
LEFT JOIN  "p2"
ON "p1"."env" = "p2"."env")

Note: you have to set “Run SQL command directly”

See Union.odb

Regards