How to combine rows of 2 tables having some column

问题:

I have two tables for user posts, one for text post and one for multimedia post, the structure of both tables is as follows.

table text_post

+--------+--------------+-----------+-----------+-----------------+-----------+
| postid | post_content | post_user | post_type | post_visibility | post_date |
+--------+--------------+-----------+-----------+-----------------+-----------+

table multimedia_post

+-------+------------+--------+---------+---------+---------------+---------+
|post_id|post_content|post_url|post_user|post_type|post_visibility|post_date|
+-------+------------+--------+---------+---------+---------------+---------+

in text_post post_content is text data that posted by user and in multimedia_post post_content is caption text that is associated with multimedia file, so basically post_content is text matter. all columns are common, only the post_url is different in multimedia_post, I want to combine result of these two table as follows

+-------+------------+--------+---------+---------+---------------+---------+
|post_id|post_content|post_url|post_user|post_type|post_visibility|post_date|
+-------+------------+--------+---------+---------+---------------+---------+
|  1    |  some text | null   | 1       | <type>  | <visibility>  | <date>  |
+-------+------------+--------+---------+---------+---------------+---------+
|  1    | img_caption| <url>  | 1       | <type>  | <visibility>  | <date>  |
+-------+------------+--------+---------+---------+---------------+---------+
..and so on

here 1st row returned is from text_post so post_url is set to null because only multimedia_post has that column...and second row is from multimedia_post as there is url of the post...

How can I achieve this?

回答1:

You can UNION ALL from two SELECTs that return columns that "line up", like this:

SELECT
    postid as post_id
,   post_content
,   null as post_url
,   post_user
... -- the rest of text_post columns included in the select
FROM text_post
UNION ALL
SELECT
    post_id
,   img_caption as post_content
,   post_url
,   post_user
... -- the rest of multimedia_post columns included in the select
FROM multimedia_post

Note how null as post_url is selected from text_post: this is necessary in order to line up the columns of the two select statements.

回答2:

Use UNION

SELECT 
  postid, post_content, NULL, post_user, post_type, post_visibility, post_date 
FROM
  text_post
UNION
 SELECT 
  postid, post_content, post_url, post_user, post_type, post_visibility, post_date 
FROM
  multimedia_post

回答3:

You have to do use full outer join on both tables

 select post_id,post_content,post_url,post_user,post_type,post_visibility,
 post_date from 
 text_post as tp, multimedia_post as mp 
 on tp.post_id = mp.post_id