Join two tables - One common column with different values

Question

I have been searching around for how to do this for days - unfortunately I don't have much experience with SQL Queries, so it's been a bit of trial and error.

Basically, I have created two tables - both with one DateTime column and a different column with values in. 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          |

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

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

I want to combine these two tables to 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     |

I have achieved this by creating a third table that 'UNION's the DateTime column from both tables and then uses that third table's DateTime column for the new table but was wondering if there was a way to skip this step out. (Eventually I will be adding more and more columns on from different tables and don't really want to be adding yet more processing time by creating a joint DateTime table that may not be necessary).

My working code at the moment:

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

Answer 1

You need a plain old FULL OUTER JOIN like this.

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

This will give you NULL for ACOQ1_Pump_Running, ACOQ2_Pump_Running for rows which do not match the date in the corresponding table. If you need 0 just use COALESCE or ISNULL.

Side Note: : In your script, I can see your are using DateTime CHAR(50). Please use appropriate types