There are 2 tables, from which you need to pull all the data, BUT by order by each table id id tried like this:

SELECT id_post AS union_id, * FROM post UNION ALL SELECT id_quote AS union_id, * FROM quote ORDER BY union_id DESC

Gives an error message:

Unknown column 'union_id' in 'order clause'

  • check the number of fields in both requests - must be the same. and, yes, * do not use, it helps only in the console when you need to look quickly at the table. in other cases, you should always write a list of fields, save yourself time on corrections and searching for errors. - Yura Ivanov
  • In the second table there are fewer fields, how to be? - foozzi
  • Even you are not doing the right thing, but you can add to select 1, 1 ... - Gorets
  • Did not understand a little - foozzi
  • add units to the required number of fields - Gorets

2 answers 2

Yes, @Gorets is right. Need a structure of your data. By the names of the tables and * it is impossible to understand what is stored there and what you want to receive in the end in one request.

Suppose that you are interested in all posts and comments on these posts in a single list.
And, say, in the list you need to display a link for the post / comment, the author, the date, maybe the topic ...

Notice that the list of fields that need to be received is formed in advance and all of them are listed in the request, and those fields that are not needed should not be mentioned. If for one table a field from another does not make sense, then instead of this field, write a value, for example, NULL . It turns out something like:

 select 'post' as source, id_post as union_id, author, post_date as _date, title from posts union all select 'quote' as source, id_quote, commenter, comment_date, NULL from quotes order by union_id;

The request will return your list. Depending on the source field, as a result, you will know which table the id will refer to - posts or quotes , because they may coincide ...

NB When using union all aliases for the sample fields, it is enough to specify for the first table, the union will go in the order of the fields, and not by the names, which is why * in the query can only harm, suddenly the structure changes.

see SQLFiddle for an example of different queries ...

Threat It would be much easier to help if the question was asked more accurately and fully.

    That's what he built, but how to be here, write all the columns that the table eats? if count does not match.

     SELECT F.*, S.* FROM post F, quote S ORDER BY F.id_post DESC, S.id_quote DESC
    • you do not do it right, I write the 2nd time, what is your table architecture? And the table with comments is a field with the id of the article? you need to make a join to this table - Gorets