Join two tables. One common column with different values.

Question

I searched for myself how to do this for several days - unfortunately, I do not have much experience working with SQL queries, so this was a small trial version and an error.

Basically, I created two tables - both with one DateTime column, and with another column with values. The DateTime column has different values ​​in each table.

So...

ACOQ1 (Table 1)
===============

| DateTime | ACOQ1_Pump_Running |
|----------+--------------------|
| 7:14:12  |         1          |
| 8:09:03  |         1          |

Run codeHide result

ACOQ2 (Table 2)
===============

| DateTime | ACOQ2_Pump_Running |
|----------+--------------------|
| 3:54:20  |         1          |
| 7:32:57  |         1          |

Run codeHide result

I want to combine these two tables so that they look like this:

| DateTime | ACOQ1_Pump_Running | ACOQ2_Pump_Running |
|----------+--------------------+--------------------|
| 3:54:20  |      0 OR NULL     |         1          |
| 7:14:12  |         1          |      0 OR NULL     |
| 7:32:57  |      0 OR NULL     |         1          |
| 8:09:03  |         1          |      0 OR NULL     |

Run codeHide result

, , "UNION DateTime" , " " , , . ( , DateTime, ).

:

CREATE TABLE JointDateTime
(
DateTime CHAR(50)
CONSTRAINT [pk_Key3] PRIMARY KEY (DateTime)
);

INSERT INTO JointDateTime (DateTime)
SELECT ACOQ1.DateTime FROM ACOQ1
UNION
SELECT ACOQ2.DateTime FROM ACOQ2

SELECT JointDateTime.DateTime, ACOQ1.ACOQ1_NO_1_PUMP_RUNNING, ACOQ2.ACOQ2_NO_1_PUMP_RUNNING
FROM (SELECT ACOQ1.DateTime FROM ACOQ1
      UNION
      SELECT ACOQ2.DateTime FROM ACOQ2) JointDateTime
LEFT OUTER JOIN ACOQ1
    ON JointDateTime.DateTime = ACOQ1.DateTime
LEFT OUTER JOIN ACOQ2
    ON JointDateTime.DateTime = ACOQ2.DateTime

Hide result

Answer 1

FULL OUTER JOIN, .

SELECT COALESCE(A1.DateTime,A2.DateTime) DateTime,ACOQ1_Pump_Running, ACOQ2_Pump_Running
FROM ACOQ1 A1 
FULL OUTER JOIN ACOQ2 A2 
    ON A1.DateTime = A2.DateTime

NULL ACOQ1_Pump_Running, ACOQ2_Pump_Running , . 0, COALESCE ISNULL.

:. script , DateTime CHAR(50).