A very complex MySQL select based on Inner Joins & Groups

Databases
1

Hello,

I have a rather complex MySQL query to generate.
We have 2 Tables:
answers which lists all the replies by members
articles which lists all the articles to which these answers are posted to

What we want to do is to generate a query which produces a list of all articles to which a member has posted an answer to, based on latest answer posted. We have this SELECT but it is not producing the results based on the latest answer posted, but it produces results based on latest article posted!

Here it is:

SELECT qq.id, qq.date_added, head_line FROM answers AS qa INNER JOIN articles AS qq ON (qa.qa_id = qq.id) WHERE qa.user_id = 55555555 GROUP BY qq.id ORDER BY qa.id DESC;

So how do you correct this to show the list of articles to which this member has posted an answer to based on article to which he has posted the last answer to?

Thanks to MySQL Gods for answering this :slight_smile:

2

… I think this is a mislabeled field. That or you’ve got the table names backwards.

It would be helpful if you could give us the schema of these two tables.

3

So… you’re looking for something to the tune of… (spitballing, probably needs refinement)

SELECT qq.id, qq.date_added, qq.head_line 
FROM articles AS qq 
INNER JOIN (
  SELECT answers.qa_id,  user_id 
  FROM answers 
  INNER JOIN (
        SELECT MAX(datetime_posted) AS lastpost, qa_id 
        FROM answers GROUP BY qa_id
   ) AS sqa 
   ON (sqa.qa_id = answers.qa_id AND answers.datetime_posted = sqa.lastpost)
   WHERE user_id = 5555555
) as qa 
ON qa.qa_id = qq.id

EDIT: Be consistent with your line separation, Marc.

4

No, that is correct.
The 2 Tables are to INNERJOIN on : qa.qa_id = qq.id

so again qa: is for the Answers Table
qq is for the Articles Table, to which Articles are Answers posted by users.

5

Sorry, but this query brings not even remotely what we want, which again are to:

list all the Articles that a user has posted an Answer to, by the headline of the Article only once, with articles listed based on which one had the latest Answer from the user.

Keeping in mind that a User could have posted 1000s of Answerrs/Comments to a given Article.
So we want to show each Article once based on which one was the latest one that the user posted an Answer to.

6

:bulb:
All anyone can do is offer a stab in the dark without seeing the schema.

7

I think @m_hutley was closer than you think. I don’t believe his middle subquery was necessary, and the date of the answer needed to be passed up to be accessible in the main query

Base on what you described, the below SHOULD get you what you want.

  • The sub-query returns the question id and latest post date for ALL the questions that the user posted to
  • The main query joins that list to the list of articles/questions by the id and sorts by the post date from the subquery

[aside]aliases are great when they make sense. qq and qa do not - at least for people not familiar with your table structure…[/aside]

SELECT question.id
     , question.head_line 
     , answers.answer_date
  FROM articles AS question
 INNER JOIN (SELECT answers.qa_id AS question_id
                  , MAX(datetime_posted) AS answer_date
               FROM answers 
             WHERE user_id = 5555555
             GROUP BY qa_id) AS answers ON question.id = answers.question_id
 ORDER BY answers.answer_date DESC
8

I mistook the phrase

to mean “which he was the last person to post an answer”, which is why the innermost query is grouping by and finding the MAX.

9

Thanks for the reply. But this is not even remotely there.
But in your/mine defense this is a very complicated query. In fact I do not think it can be done in MySQL since:
1st, we have to list all the replies based on ORDER BY id DESC
2nd, we need to Group these replies by question id

So ordering must come before Group by for it to be functionally correct, and then Group by must apply, again for it to be functionally correct, and this just does not work in SQL.

And just to be complete, then as 3rd step we need to JOIN this result set with the question table to get the Title & Description, etc…

So i think only way to do this is via MySQL & Php.
But thanks anyway to all.

10

well, this finally woke me up, and i do like a good challenge

so i went back to post #1 and i don’t really understand the requirement

“a list of all articles to which a member has posted an answer to” – this is pretty straightforward

“… based on latest answer posted” – based on? what does this mean???

also, please post the table layouts

4 Likes
11

Ah it is r937! If any one can possibly come up with a solution to this MySQL riddle would be you :slight_smile:

So " based on latest answer posted” that is clear! So each members posts replies/answers to questions/articles/discussions, lets call them discussions.
They can post 100s of replies to a given discussion.
We want to have a list which 1st is based on the latest replies they have posted, and then find to which discussion these replies where posted to.

and in most simple form, the lay outs are:

discussion Table: id, date_posted, title, details, user_id
replies Table: id, time_posted, user_id, qa_id

so qa_id is the id of discussion Table in replies Table
and use_id is the id of members that posted that item.

12

I mean… which is it? Give us the ACTUAL schema.

Obviously not, since none of the 3 people who have attempted to answer this thread have actually been able to decipher what you’re actually asking for.

13

OK, I’m betting @r937 will be able to come up with a MUCH more elegant solution, but I needed a distraction today, so I’m going to try again.

So just to be sure I understand ALL the requirements…

  • ALL replies are to be listed
  • Those replies need to be ordered most recent to oldest
  • HOWEVER, those replies are to be put together by the question/article/discussion they belong to. So if reply 5,3,1 belong to question 1 and 4 and 2 belong to question 2, then question 1 should be first showing 5 then 3 then 1 THEN question 2 showing 4 then 2.

If that is correct, then this SHOULD get you to what you want.

SELECT d.id
     , d.title
     , r.time_posted
     , r.id
  FROM discussion d
  JOIN replies r ON r.qa_id = d.id
  JOIN (SELECT qa_id
             , MAX(time_posted) AS latestReply
          FROM replies
         WHERE user_id = 55555
         GROUP BY qa_id) SQ ON SQ.qa_id = d.id
  ORDER BY latestReply DESC
         , r.time_posted DESC;
14

Dave,

the 1st key requirement is that we order the replies by the order of the latest posted AND then Group them by the discussion id.

So this inner SELECT will defeat this purpose:

SELECT qa_id, MAX(time_posted) AS latestReply FROM replies WHERE user_id = 55555 GROUP BY qa_id

hence producing totally invalid results.

And in reply to your list above:
. NO, ALL replies are not to be listed
we just want to show to the users the list of discussions they are engaged in based on the latest discussion to which they have posted a reply to.

15

that’s not what i understood – all ARTICLES are to be listed, not the replies

16

so the 1st article listed is the latest one the member has replied to

what about the other articles? what order should they be listed in?

17

This makes NO sense. How do you sort orders by latest posted then group them by discussion id?

You are going to HAVE to post sample tables with raw data, then what you want the end result to be.

1 Like
18

no… that’s an attempt at a solution, not a requirement

the only requirement that i’ve seen so far is: list the ARTICLES a member has replied to, with the article he most recently replied to coming first

explain how to sort the other articles and i’ll give it a try

19

Here again is the Job:

  • We have a Table of discussions, to which discussion a member can post 100s of replies to
  • We have a Table of replies

So we want to List all the discussions to which a member has posted a reply to, sorted by those discussions to which he/she has posted the latest replies to

20

OK…Since multiple people aren’t getting your requirement, let’s try it this way…

If you have this data in your tables, what is the desired output? Please show an example

Discussions
id, date_posted, title, details, user_id
1,'1/1/2019', 'Discussion 1', 'These are the details for discussion 1', '12345'
2,'1/1/2019', 'Discussion 2', 'These are the details for discussion 2', '12345'
3,'1/1/2019', 'Discussion 3', 'These are the details for discussion 3', '12345'
4,'1/1/2019', 'Discussion 4', 'These are the details for discussion 4', '12345'
5,'1/2/2019', 'Discussion 5', 'These are the details for discussion 5', '12345'
6,'1/2/2019', 'Discussion 6', 'These are the details for discussion 6', '12345'
7,'1/2/2019', 'Discussion 7', 'These are the details for discussion 7', '12345'
8,'1/2/2019', 'Discussion 8', 'These are the details for discussion 8', '12345'
9,'1/2/2019', 'Discussion 9', 'These are the details for discussion 9', '12305'

Replies
id, time_posted, user_id, qa_id
1,'1/1/2019 11:00', 55555, 1
2,'1/1/2019 11:01', 55555, 9
3,'1/1/2019 11:02', 55555, 7
4,'1/1/2019 11:03', 55555, 1
5,'1/1/2019 11:04', 55555, 7
6,'1/1/2019 11:05', 55555, 7
7,'1/1/2019 11:06', 55555, 1
8,'1/1/2019 11:07', 55555, 3
9,'1/2/2019 11:08', 11111, 2
10,'1/2/2019 11:09', 11111, 1
11,'1/2/2019 11:10', 11111, 3
12,'1/3/2019 11:11', 55555, 3
13,'1/3/2019 11:12', 55555, 1
14,'1/3/2019 11:13', 55555, 4
15,'1/3/2019 11:14', 11111, 2
16,'1/3/2019 11:15', 55555, 3
17,'1/4/2019 11:16', 55555, 5
18,'1/5/2019 11:17', 55555, 1
19,'1/5/2019 11:18', 55555, 6
20,'1/5/2019 11:19', 55555, 1
21,'1/5/2019 11:20', 55555, 8