SQL Outer Join while conditioning on an extra column

Question

I am trying to pull up an outer join between two tables, on a specifc column "Identifier", however, I would only like to carry out the outer joins on the rows where additional column "Date" matches between the tables. Example of what I am trying to achieve is, for the two tables below:

+------------+---------------+-------------------+-----------------+
| Date | Identifier | TableA-Attribute1 |TableA-Attribute2|
+------------+---------------+-------------------+-----------------+
| date1 | Id1 | a | x |
| date1 | Id2 | b | y |
| date1 | Id3 | c | z |
| date2 | Id1 | d | x |
| date2 | Id2 | e | y |
+------------+---------------+-------------------+-----------------+
+------------+---------------+-------------------+-----------------+
| Date | Identifier | TableB-Attribute1 |TableB-Attribute2|
+------------+---------------+-------------------+-----------------+
| date1 | Id1 | aa | xx |
| date1 | Id2 | bb | yy |
| date1 | Id3 | cc | zz |
| date1 | Id4 | dd | xx |
| date2 | Id1 | ee | yy |
+------------+---------------+-------------------+-----------------+

I need to end up with something that looks like this:

+------------+---------------+---------+-------+--------+-------+
| Date | Identifier | A-Attr1 |A-Attr2|B-Attr1 |B-Attr2|
+------------+---------------+---------+-------+--------+-------+
| date1 | Id1 | a | x |aa |xx |
| date1 | Id2 | b | y |bb |yy |
| date1 | Id3 | c | z |cc |zz |
| date1 | Id4 | - | - |dd |xx |
| date2 | Id1 | d | x |ee |yy |
| date2 | Id2 | e | y |- |- |
+------------+---------------+---------+-------+--------+-------+

I was trying to do something like this

go select * from table1 full outer join (select * from table2 where table1.date=table2.date)

however, that unfortunately does not seem to be working.

• ypercubeᵀᴹ: What is the DBMS? Is it SQL Server?

Answer 1

You don't need subqueries. You only need to use both columns in the ON condition of the FULL JOIN:

SELECT
 -- t1.*, -- adjust, keep only 
 -- t2.*, -- needed columns
 COALESCE(t1.date, t2.date)
 AS date, 
 COALESCE(t1.identifier, t2.identifier)
 AS identifier, 
 t1.attribute1 AS a_attribute1,
 t1.attribute2 AS a_attribute2,
 t2.attribute1 AS b_attribute1,
 t2.attribute2 AS b_attribute2
FROM table1 AS t1 
 FULL JOIN table2 AS t2
 ON t1. date = t2. date
 AND t1. identifier= t2. identifier
-- WHERE
-- (t1.date = '2000-01-01' OR t2.date = '2000-01-01')
 ;

If we want date and identifier combined in one column each, we can use COALESCE() function.

• user154312: Many thanks for the answer. Can I just follow up on this - Do you maybe know how can I filter the results by date. I've been trying to do that by just doing--> select * from (your query) as p where p.date='2000年01月01日', but I am getting the Error message "The column 'date' was specified multiple times for 'p'."
• ypercubeᵀᴹ: See the edit. Don't use t1.^, t2.*, keep only the columns you need.
• ypercubeᵀᴹ: Filtering on a FULL join can be tricky. You need to either filter on both tables (see my example in the answer) or use filters in derived tables first and then (full) join.