Combining Two Tables Into One 2

[larrydavid]

Hello,

I have two tables with identical columns and datatypes. One table has YTD - Current results and the other has YTD - Previous results so there are a few columns in each that are named to reflect this i.e. Table1 has [Col_1_Current] and Table2 has [Col1__Previous]. I need to combine these two tables into one so that all the columns from each table are aligned:

[Col_1_Curr], [Col_2_Curr], [Col_3_Curr], [Col_1_Prev], [Col_2_Prev], [Col_3_Prev]

I've tried doing a CROSS JOIN but got duplicate records returned. There is a common key field for both tables, however this key field value may or may not exist in both tables (usually it will). One table has 56067 records and the other has 50769 records, so I'm not sure I want to do a join because that might exclude records.

I tried a UNION, but that didn't get me the results with the columns aligned as demonstrated above. So, at this point I'm not sure how to approach this. If anyone can help I would be much obliged and grateful for your time and expertise.

Thanks,
Larry

 

[markros]

Most likely you need a FULL JOIN based on the common field. With FULL JOIN you will not lose any record.

PluralSight Learning Library

 

[Olaf Doschke]

The one table having 5 records more may simply reflect the fact, that for some YTD results, there is only current and not previous results. Or only previoius and no current results exist.

Do a OUTER JOIN of both tables joining on the common field, and you get NULL for missing records, but either Current or Previous fields will be field. No records are suppressed or lost, all data is in the joined table.

Bye, Olaf.

 

[Olaf Doschke]

FULLL OUTER JOIN that is, and that's the same as a FULL JOIN, so I am not saying anything else than markros.

Bye, Olaf.

 

[larrydavid]

Excellent, thank you both very much.