How do I add DESC to this query?

Question

I'm doing a project and I'm using MYSQLI I just need to make this descend. How can I do this since the usual way on how I do this is not working.

SELECT * FROM posts 
LEFT JOIN members ON posts.user_id = members.user_id 
UNION 
SELECT * FROM posts 
RIGHT JOIN members ON posts.user_id = members.user_id 
WHERE posts.user_id IS NOT NULL

This is what I tried

SELECT * FROM posts
  LEFT JOIN members
    ON posts.user_id = members.user_id
UNION
SELECT *
  FROM posts
  RIGHT JOIN members
    ON posts.user_id = members.user_id
  WHERE posts.user_id IS NOT NULL AND
  ORDER BY posts.user_id DESC

Possible duplicate of [Using union and order by clause in mysql](https://stackoverflow.com/questions/3531251/using-union-and-order-by-clause-in-mysql) — Badiparmagi, Nov 29 '17 at 05:22
This is what I tried SELECT * FROM posts LEFT JOIN members ON posts.user_id = members.user_id UNION SELECT * FROM posts RIGHT JOIN members ON posts.user_id = members.user_id WHERE posts.user_id IS NOT NULL AND ORDER BY posts.user_id DESC but it's not working and what do you mean by top half of that query? — , Nov 29 '17 at 05:30
`a` *top half* if you divide up the sql with some line breaks (*which is conventional for those who deal with SQL all the time*, I really don't know why folks think a single row of sql code is helpful especially in a question). `b` the second part of your query achieves nothing useful. — Paul Maxwell, Nov 29 '17 at 05:43
I formatted the second query. You have a hanging `AND` that isn't supposed to be there, in the line before the `ORDER BY`. Fix that up and you'll be in better shape. — Bob Jarvis - Слава Україні, Jun 20 '20 at 22:14

Paul Maxwell · Answer 1 · 2017-11-29T06:51:30.317

2

IF you require ALL members with or without posts, then revers the table relationships so that the posts are left joined to members:

SELECT * 
FROM members 
INNER JOIN posts ON members.user_id = posts.user_id
ORDER BY members.user_id DESC, posts.id ASC ## I am guessing some column names

IF you have members with no posts AND posts with no members THEN you want the equivalent of a "full outer join" and this does require a UNION... although I seriously doubt the need for this here I include it for completeness:

 SELECT * ## MUST choose the columns!!
 FROM (
    SELECT posts.*, members.* ## MUST choose the columns!!
    FROM members 
    LEFT JOIN posts ON members.user_id = posts.user_id
    UNION
    SELECT  posts.*, members.* ## MUST choose the columns!!
    FROM posts 
    LEFT JOIN members ON posts.user_id = members.user_id 
    ) d
ORDER BY user_id DESC, posts_id ASC ## I am guessing some column names

----

If you only require posts which have an associated user_id then I suggest you try this:

SELECT * 
FROM posts 
INNER JOIN members ON posts.user_id = members.user_id 
ORDER BY members.user_id DESC, posts.id ASC ## I am guessing some column names

If you do need posts without a user_id then suggest you try this:

SELECT * 
FROM posts 
LEFT JOIN members ON posts.user_id = members.user_id 
ORDER BY ISNULL(members.user_id) ASC, members.user_id DESC

The second part of your initial query will not add more rows to the final outcome. Consider the following test:

CREATE TABLE members
    (`user_id` int);

INSERT INTO members
    (`user_id`)
VALUES
    (1);

CREATE TABLE posts
    (`id` int, `user_id` int);

INSERT INTO posts
    (`id`, `user_id`)
VALUES
    (1, 1),
    (2, NULL),
    (3, NULL);

SELECT * FROM posts 
LEFT JOIN members ON posts.user_id = members.user_id;

id | user_id | user_id
-: | ------: | ------:
 1 |       1 |       1
 2 |    null |    null
 3 |    null |    null

SELECT * FROM posts 
RIGHT JOIN members ON posts.user_id = members.user_id 
WHERE posts.user_id IS NOT NULL;

id | user_id | user_id
-: | ------: | ------:
 1 |       1 |       1

SELECT * 
FROM posts 
LEFT JOIN members ON posts.user_id = members.user_id 
ORDER BY ISNULL(members.user_id) ASC, members.user_id DESC;

id | user_id | user_id
-: | ------: | ------:
 1 |       1 |       1
 2 |    null |    null
 3 |    null |    null

dbfiddle here

edited Nov 29 '17 at 06:51

answered Nov 29 '17 at 05:32

Paul Maxwell

33,002
3
32
51

I'm making a news feed post like facebook I tried your suggestion but it's kind of strange the way I had it was perfect but the only complaint I had was that It was in ASC order but I tried your way but now it's going based on random order perhaps I think I know the solution how can I make this go by the post_id instead in DESC order but at the same time I need posts and members to match user_id. Since user_id is in both tables. – Nov 29 '17 at 05:56
Well if you must have a match by user_id **use an INNER JOIN** then you can remove the `isnull()` from the order clause and add post_id as well – Paul Maxwell Nov 29 '17 at 05:58
by the way, you were getting the ascending order from your original query because of the union **BUT** you cannot rely on union to produce a result in any order. To get a reliable order from any SQL query you **MUST** use an `order by` clause. – Paul Maxwell Nov 29 '17 at 06:01
I see well I tried your suggestion about this SELECT*FROM posts INNER JOIN members ON posts.user_id = members.user_id ORDER BY members.user_id DESC, posts.id ASC but PHPMYADMIN said this #1054 - Unknown column 'posts.id' in 'order clause' – Nov 29 '17 at 06:14
I am guessing becuse you use "select *" just use the correct column name - I only know your db the the extent of what you include on this page – Paul Maxwell Nov 29 '17 at 06:15
The reason why I need to use union because PHPMYADMIN don't have a full join feature that's why I had that strange structure from my post. I need to emulate a full join in desc order I been searching on google and trying your suggestions but still no change how I want it. I just need it to emulate what I had but in DESC order. – Nov 29 '17 at 06:33
Well i'm just going based on what some other guy told me online that's why I'm using that and left or right join or inner join is not the same as a full emulation of a full join but ok man peace thanks for trying to help me out. – Nov 29 '17 at 06:36
If you have 1,000,000 posts and left join to members you will get 1,000,000 rows returned. If you then union to posts right joined to members you will still get 1,000,000 rows returned. I repeat, the second part of your query "does nothing useful" (at all) – Paul Maxwell Nov 29 '17 at 06:38
One last attempt, I will add it to the answer shortly. – Paul Maxwell Nov 29 '17 at 06:39

score 0 · Answer 2 · edited Jan 20 '18 at 10:47

0

Instead of using left and right join separately I would suggest you to use full outer join. And also remove the AND which is placed before ORDER.

So that your query will be like

SELECT * FROM POSTS FULL OUTER JOIN MEMBERS ON POSTS.user_id = MEMBERS.user_id 
WHERE POSTS.user_id  IS NOT NULL 
ORDER BY POSTS.user_id DESC;

edited Jan 20 '18 at 10:47

halfer

19,824
17
99
186

answered Nov 29 '17 at 07:03

praveen kumar

23
2

Thanks for your help but a FULL OUTER JOIN is not supported in PHPMYADMIN that's why I had it the way I did to hopefully emulate a full outer join. – Nov 29 '17 at 07:38

How do I add DESC to this query?

2 Answers2

----