0

I'm trying to make a list of user related discussions.

Imagine, if you will:

posts
-------------------------------------------------------------------
ID | PID | FID | TITLE                                    | USER
1  |  0  |  1  | Hello World                              | User 1
2  |  0  |  23 | Endangered Squirrels & How to Cook Them  | Eddy
3  |  1  |  1  | Re: Hello World                          | Eddy
4  |  1  |  1  | Re: Hello World                          | Clark
5  |  0  |  3  | Any Vacation Suggestions?                | Clark
6  |  5  |  3  | Re: Any Vacation Suggestions?            | Eddy
7  |  5  |  3  | Re: Any Vacation Suggestions?            | Clark
8  |  5  |  3  | Re: Any Vacation Suggestions?            | Ellen

To show all of Eddy's posts I was using a query selecting posts with DISTINCT parents ids(pid) and thought it was working. I soon realized that 0 was only consider DISTINCT once and if he had created a new topic and not replied, it wasn't being listed if it wasn't his first.

So how do I get a list of ids with DISTINCT pid and pid != 0, and join it with a query of ids that have pid = 0 where the user = Eddy

This is the first question I've ever asked so forgive me if it's not perfect.

UPDATED WITH QUERY

I have since switched to using GROUP BY so I can fetch the post's id, but the issue is still the same

I was asked to provide my query. This is the one I'm using that only provides 1 row with pid as 0. I also updated my table layout above to show forum id, in the query below it only pulls topics from a list of public forums.

SELECT id, pid FROM posts WHERE fid IN (1,2,3) AND author = 'Eddy' GROUP BY pid ORDER BY IF(latest > timestamp, latest, timestamp) DESC LIMIT 1, 5;

thank you for any and all help.

Tayyab Khan
  • 283
  • 3
  • 11
user3502782
  • 1
  • 2
  • Post your query as well. – DEarTh Apr 06 '18 at 06:50
  • Instead of trying to describe code in natural language, show the actual code. You might be interested in learning [how to ask a good question](https://stackoverflow.com/help/how-to-ask) and [how to create a minimal, complete and verifiable example](https://stackoverflow.com/help/mcve). – Binarus Apr 06 '18 at 06:50
  • Try to look at the following answers - https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query I think it's the same. – Sergey Menshov Apr 06 '18 at 09:36
  • It's similar, but I'm not looking for ALL children. I want the ids of every parent the author created, as well as 1 child id - that the author created - per parent. – user3502782 Apr 08 '18 at 04:24

1 Answers1

0

I was able to do what I wanted with a simple IF in the GROUP BY, it was so simple. 

SELECT id, pid
FROM posts
WHERE fid IN (1,2,3)
AND author = 'Eddy'
GROUP BY IF(pid > 0, pid, id)
ORDER BY IF(latest > timestamp, latest, timestamp) DESC
LIMIT 1, 5;
Arjan
  • 9,784
  • 1
  • 31
  • 41
user3502782
  • 1
  • 2