Combine 2 tables with identical columns

Question

Have looked all over stackoverflow and Google and have come close, but haven't found a complete solution yet...maybe it's a simple solution no one's needed to ask for help before?

I have 2 tables of courses & course related info (Master_Table & Import_Table). Master_Table table contains the core content/info on each course but is missing dates & times as these change weekly (there are currently about 240 rows of core courses). See Master_Table structure below:

Master_Table

+-----------+----------------+------------+------------+-------------------+------+
| sku_crmid |  post_title    | start_date | start_time |   post_content    | u_id |
+-----------+----------------+------------+------------+-------------------+------+
| skuid1    | Unique Title 1 |    null    |    null    | Unique Content 1  | null |
| skuid2    | Unique Title 2 |    null    |    null    | Unique Content 2  | null |
| skuid2    | Unique Title 3 |    null    |    null    | Unique Content 3  | null |
+-----------+----------------+------------+------------+-------------------+------+

Import_Table is updated weekly with new dates & times (could be as many as 2000+ rows/import). It also contains post_tile...the post_title is the ONLY thing unique between the 2 tables. While a post_title will show once in Master_Table, it will show several times in Import_Table (one row for each new date & time). See Import_Table structure below:

Import_Table

+-----------+----------------+------------+------------+-------------------+------+
| sku_crmid |  post_title    | start_date | start_time |   post_content    | u_id |
+-----------+----------------+------------+------------+-------------------+------+
|   null    | Unique Title 1 | 12/02/2013 |   8:00am   |     null          | null |
|   null    | Unique Title 1 | 12/16/2013 |   8:00am   |     null          | null |
|   null    | Unique Title 2 | 12/09/2013 |   8:00am   |     null          | null |
|   null    | Unique Title 2 | 12/16/2013 |   8:00am   |     null          | null |
|   null    | Unique Title 3 | 12/02/2013 |   8:00am   |     null          | null |
|   null    | Unique Title 3 | 12/09/2013 |   8:00am   |     null          | null |
+-----------+----------------+------------+------------+-------------------+------+

I need to be able to combine both Master_Table and Import_Table into one table/output that I can export to CSV. If there is a "null" in the Import_Table and data in the Master_Table, I need that "null" replaced with data in the output. If "null" exists in both tables in the same column, then "null" out is OK.

Below are 2 possible outputs that would be OK if they are even possible. Option 1 is OK and would work, but Option 2 is more ideal. I have seen option 2 done with inserting a ', ' in a query before, but a pipe is what I would need with no space on either side of the '|'...

output needed - option 1

+-----------+----------------+------------+------------+-------------------+------+
| sku_crmid |  post_title    | start_date | start_time |   post_content    | u_id |
+-----------+----------------+------------+------------+-------------------+------+
|   null    | Unique Title 1 | 12/02/2013 |   8:00am   |  Unique Content 1 | id_1 |
|   null    | Unique Title 1 | 12/16/2013 |   8:00am   |  Unique Content 1 | id_2 |
|   null    | Unique Title 2 | 12/02/2013 |   8:00am   |  Unique Content 2 | id_3 |
|   null    | Unique Title 2 | 12/16/2013 |   8:00am   |  Unique Content 2 | id_4 |
|   null    | Unique Title 3 | 12/02/2013 |   8:00am   |  Unique Content 3 | id_5 |
|   null    | Unique Title 3 | 12/09/2013 |   8:00am   |  Unique Content 3 | id_6 |
+-----------+----------------+------------+------------+-------------------+------+

output needed - option 2 (best possible solution)

+-----------+----------------+-------------------------------------+-------------------+------+
| sku_crmid |  post_title    |          start_date_time            |   post_content    | u_id |
+-----------+----------------+-------------------------------------+-------------------+------+
|   skuid1  | Unique Title 1 | 12/02/2013 8:00am|12/16/2013 8:00am | Unique Content 1 | id_1 |
|   skuid2  | Unique Title 2 | 12/09/2013 8:00am|12/16/2013 8:00am | Unique Content 2  | id_2 |
|   skuid3  | Unique Title 3 | 12/02/2013 8:00am|12/09/2013 8:00am | Unique Content 3  | id_3 |
+-----------+----------------+-------------------------------------+-------------------+------+

So in the end, I need a way to do the following (if possible):

1. Combine both tables into one and assign a unique_id so I can export as a CSV

2. Assign a unique_id generation in a way that will never repeat

3. Combine all start_dates & start_times to one line/cell and separate them with a pipe (|) and no spaces on either side of the pipe

4. If the pipe solution isn't an option...option 1 above is OK too...

If it helps I am using MySQL

Answer 1

Try

SELECT * FROM Master_Table AS M 
JOIN Import_Table AS I ON I.post_title = M.post_title

This will join both of your tables on their common post titles. you can change * to select the columns you need.

Answer 2

You can assign a unique key using the uuid() function. Alternatively, you have to use variables, as in the following example.

The key to the query is joining the master table to the input table and then aggregating the rows down to one row.

The query you want is something like:

select coalesce(it.sku_crmid, mt. sku_crmid) as sku_crmid,
       mt.post_title,
       group_concat(concat(it.start_date, it.start_time) separator '|') as start_date_time,
       coalesce(it.post_content, mt.post_content) as post_content,
       @rownum := @rownum + 1 as uid
from Master_table mt left outer join
     Import_Table it
     on it. post_title = mt. post_title cross join
     (select @rownum := 0) const
group by post_title;

I say "something like" because of the issue of date/time formats. You might want to format those columns differently.