SQL -- merge only some columns from different tables

Question

I have two tables which i would like to turn into one in a SELECT query by merging some columns together while having other columns remaining distinct.

first table (posts_article)

 id_post   id_user   title   content   published   nbr_comments   timestamp
 ---------------------------------------------------------------------------
 1         15        title1  text1     1           23             1111111111
 2         20        title2  text2     0           54             1122334455

second table (posts_text)

 id_post   id_user   message   nbr_comments   timestamp
 -------------------------------------------------------
 1         17        message1  15             1234567891
 2         22        message2  0              1987654321

expected result

 id_post   id_user   title   content   message    published   nbr_comments   timestamp
 --------------------------------------------------------------------------------------
 1         15        title1  text1                1           23             1111111111
 2         20        title2  text2                0           54             1122334455
 1         17                          message1               15             1234567891
 2         22                          message2               0              1987654321

I've tried a few things but i can't really solve this. how can I get the expected result? What is the most efficient way to select that result?

EDIT: The logic of this merge is to be able to get all the different types of "posts" (submitted by users) at the same time.

Thanks.

Could you state more precisely what you want your join/merge logic to be? The example is pretty nice, but we're missing the purpose statement. — Romain
– Romain, Commented Jan 10, 2012 at 8:47
I just edited the post. (Not sure if I should've of answered or commented or whatever, I'm quite new to stackoverflow.) — oliv23
– oliv23, Commented Jan 10, 2012 at 9:28

fyr · Accepted Answer · 2012-01-10 08:56:16Z

1

Try:

SELECT id_post, id_user,  title,  content, NULL AS message , published, nbr_comments, timestamp FROM posts_article
UNION
SELECT id_post, id_user, NULL as title, NULL as content,  message, NULL as published, nbr_comments, timestamp FROM posts_text

edited Jan 10, 2012 at 8:56

answered Jan 10, 2012 at 8:49

fyr

21k7 gold badges40 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

oliv23 Over a year ago

Thanks it works great but do you think it's the best way - ressource wise - to to this?

fyr Over a year ago

Usually if you are in the position to change your model it is best to do so. Imho every UNION is based on a design-flaw. If you are not able to change the model because the project is a brownfield project or something like this you need to live with it and the limitations.

oliv23 Over a year ago

Well the thing is basically users can post articles or short messages or pictures for example and I store each type of "post" in its table. So, to me, there are only three solutions to the problem of retrieving all the posts: 1)have as many requests as there are post types 2) UNION all the requests to get all posts at once 3) Store all types of posts in one database ressembling the "expected result" in this question and SELECT from there.

fyr Over a year ago

Ok you need to go with 1 or 2 .. 3 is no option however an alternative would be to use something like a document oriented approach to this(e.g. mongodb).

oliv23 Over a year ago

Yeah well that's the same conclusion I came to. Except that 2) seems better in the sense that i can sort the data with MySQL which is faster than with PHP. As to mongodb, i'll check it out, thanks.

Collectives™ on Stack Overflow

SQL -- merge only some columns from different tables

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related