1

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.

  • What is the DBMS? Is it SQL Server? – ypercubeᵀᴹ Feb 17 at 10:03
2

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.

  • 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'." – user154312 Feb 17 at 9:47
  • See the edit. Don't use t1.^, t2.*, keep only the columns you need. – ypercubeᵀᴹ Feb 17 at 9:55
  • 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. – ypercubeᵀᴹ Feb 17 at 10:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.