Wsrtjtyk

I have two tables (first two shown) and need to make a third from the first two - do I need to do a join or can you reference a table without joining?

The third table shown is the desired output. Thanks for any help!

| ACC | CALL DATE |  |  |

+-----+-----------+--+--+

| 1 1 | 2/1/18    |  |  |

+-----+-----------+--



+-----+---------------+--+--+

| ACC | PURCHASE DATE |  |  |

+-----+---------------+--+--+

| 1 1 | 1/1/18        |  |  |

+-----+---------------+--+--+



+-----+-----------+----------------------+--+

| ACC | CALL DATE | PRIOR MONTH PURCHASE |  |

+-----+-----------+----------------------+--+

| 1 1 | 2/1/18    | YES                  |  |

+-----+-----------+----------------------+--+

Of course you can have a query that references multiple tables without joining. union all is an example of an operator that does that.

There is also the question of what you mean by "joining" in the question. If you mean explicit joins, there are ways around that -- such as correlated subqueries. However, these are implementing some form of "join" in the database engine.

As for your query, you would want to use exists with a correlated subquery:

select t1.*,

       (case when exists (select 1

                          from table2 t2

                          where t2.acc = t1.acc and

                                datediff(month, t2.purchase_date, t1.call_date) = 1

                         )

             then 'Yes' else 'No'

        end) as prior_month_purchase

from table1 t1;

This is "better" than a join because it does not multiply or remove rows. The result set has exactly the rows in the first table, with the additional column.

The syntax assumes SQL Server (which was an original tag). Similar logic can be expressed in other databases, although date functions are notoriously database-dependent.

Lets check the options,

Say if you were to create a new third table on the basis of the data in first two, then every update/inserts/deletes to either of the tables should also propagate into the third table as well.

Say you instead have a view which does what you need, there isnt a need to maintain that third table and also gets you the data needed from the first two each time you query it.

create view third_table as

   select a.acc,a.call_date,case when dateadd(mm,-1,a.call_date)=b.purchase_date then 'Yes' else 'No end as prior_month_purchase

     from first_table a

left join second_table b

      on a.acc=b.acc