A very complex MySQL select based on Inner Joins & Groups

here ya go –

SELECT d.title
     , d.details
     , MAX(r.time_posted) AS latest
  FROM replies AS r
INNER
  JOIN discussion AS d
    ON d.id = r.qa_id
 WHERE r.user_id = 210937
GROUP
    BY d.title
     , d.details
ORDER
    BY latest DESC

OMG Man. You got it r937 :slight_smile:
You are the MySQL Guru man :slight_smile:
I mean I am real good with mySQL commands, but you are above and beyond. Cool

r93, actually there is a Lil problem still.
That is if the user has posted more than 1 reply on a given day, then the above query does not list their latest discussion to which they posted the latest reply to above the discussions to which he/she posted earlier replies to. That is the discussions are sorted correct based on his/her replies posted on daily basis, but NOT within the same day not.
And I tried this and still no good:

replace: MAX(r.time_posted) AS latest

with: MAX(r.id) AS latest

i know this is English but i cannot understand it at all

unless you can explain it better, i’m afraid i’m going to want to see comprehensive test data which demonstrates your problem

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.

But if the user has posted many replies in a given Day, then the above Query does NOT list the discussions to which he/she has posted replies to based on the latest discussions to which he/she had posted replies to.
It does so when days replies are posted are different, but NOT when the day of the replies are same.

Keep in mind that replies are timestamped, so they are recorded not just by day/month/year but also by Minute & Second of when they were posted.

I hope that makes it as clear as can be.

nope

perhaps you do not understand how MAX() works

each discussion that a user has replied to is included, yes?

MAX() returns the latest reply, no matter if there’s more than one per day

and now, it really is time for you to post comprehensive sample data

2 Likes

Well to see comprehensive sample data, is not so easy, cause:
1- you need to become Anoox member. This is free and takes less than a minute.
2- But then you need to post a whole bunch of answers to member questions & discussions and then check under: “My Conversations” to see this sample data

Because what is interesting, is that IF the member has posted a few messages, then their “My Conversations” will list correctly those discussions that they have posted a reply to based on the discussions to which they have posted latest reply to. But if they have posted many replies, such as 100s and 1000s then it breaks down!

FYI: i have found a 100% solution to this using MySQL & Php. But MySQL alone does not produce 100% accurate data!!!
And FYI we are currently at this MySQL query to get this done:

SELECT d.id, d.user_id, d.date_added, d.head_line, d.viewed, d.category, MAX(r.id) AS latest
FROM replies AS r
INNER JOIN discussions AS d
ON d.id = r.qa_id
WHERE r.user_id = $user_id
GROUP BY d.id
ORDER BY latest DESC

this depends on who writes the queries and their skill level

yup, and i’m saying it gives you waht you asked for

comprehensive test data, please

you don’t have to use live data, but you do have to provide representative data in those tables which will show that the query isn’t working

otherwise, i’m done here

See this screen shot:
As you can see, hopefully you can, this member posted their latest reply to:
#6 : [It is safe to invest in Cryptocurrencies such as BitCoin?]
at 32 minutes 9 seconds ago

However, this discussion is being listed as number 6 in the list of their conversations and not as #2, which has a reply from them to:

[Is it rude to ask a girl on a 1st date that we go Dutch?]
which they posted a reply to: 38 minutes 18 seconds ago

An easy way to help others to be able to help is to post something like this that can be run in the console window:

CREATE TABLE test_discussions ( 
// definition here 

CREATE TABLE test_replies ( 
// definition here 

INSERT INTO test_discussions 
( // columns ) 
VALUES ( // values ) , ( // values ) , ( // values ) 

INSERT INTO test_replies  
( // columns ) 
VALUES ( // values ) , ( // values ) , ( // values ) 

SELECT // your query 

DROP TABLE test_discussions, test_values 

No need for it to have a lot of values or for them to be real. Only that there is enough to demonstrate the problem and they are representative.

yup, looks like a problem

are you claiming that my query produced those results?

Well your query exactly as you gave, was producing little worse results.
So I modified it to this:

SELECT d.id, d.user_id, d.date_added, d.head_line, d.viewed, d.category, MAX(r.id) AS latest
FROM replies AS r
INNER JOIN discussions AS d
ON d.id = r.qa_id
WHERE r.user_id = $user_id
GROUP BY d.id
ORDER BY latest DESC

r9, I have found the problem. Well the solution :slight_smile:
Lil convoluted to explain, but above MySQL code is actually fine.

Thanks for your help in this matter :slight_smile:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.