Jump to content

Help For Building This Query.


manishk3008

Recommended Posts

I have this database table named messages:

 

global_ref_id int(12)

to int(12)

from int(12)

message text

status int(1)

viewed int(1)

 

where global_ref_id is id of messages (foreign key from table events), to is user id of user who received message, from is user id of user who send the message.

I want to generate a query which gives me result displaying latest message from each user to a user (say whose user_id is 192) also giving total number of messages from each user to user 192.

Till now I came up with this query to get results:

SELECT
messages.*,events.event_time, COUNT(messages.from) as "Total number of Messages to User"
FROM
messages, events
WHERE
events.global_id = messages.global_ref_id
AND messages.to = 192
GROUP BY messages.from
ORDER BY events.event_time DESC

 

now problem is that, this query is displaying the oldest message instead of latest message what I need is Latest message.

Using MYSql version:5.5.25a

 

For easy understanding I have created table in sql fiddle:

 

http://sqlfiddle.com/#!2/c68dc/7

Link to comment
Share on other sites

if the problem is that the oldest date is coming back per group then selecting the MAX() value of Event_Time per the grouping should help, something like this :

SELECT messages.global_ref_id,
messages.`to`,
messages.`from`,
messages.message,
messages.status,
messages.viewed,
latest_events.last_event,
count(*) as `TOTAL NUMBER OF MESSAGES TO USER`
FROM messages INNER JOIN
(SELECT global_id, MAX(event_time) as last_event FROM events GROUP BY event_creator)
as latest_events
ON(messages.global_ref_id = latest_events.global_id)
WHERE messages.`to` = 192
GROUP BY messages.`from`
ORDER BY latest_events.last_event DESC

 

However, if it's only displaying in the wrong order, change the DESC in the order by to ASC to reverse which way it is being ordered.

Link to comment
Share on other sites

I only realised after I posted that I could actualy play with your fiddle (I can't be the only one thinking that sounds wrong...). Being able to use your tables and sample data I came up with this:

SELECT messages.global_ref_id,
messages.`to`,
messages.`from`,
messages.message,
messages.status,
messages.viewed,
events.event_time,
count_table.msg_count as `total Number of messages`
FROM
messages
INNER JOIN
(select max(global_ref_id) as ref_id, count(*) msg_count from messages group by `from`)
as count_table
on (messages.global_ref_id = count_table.ref_id)
INNER JOIN events
on (events.global_id = messages.global_ref_id)
where messages.`to` = 192
order by events.global_id DESC

 

It preserves the overall message count while only showing the most recent record activity. I hope that's what you are after.

Link to comment
Share on other sites

That worked like charm!!!

There is one more thing I would like to add to query, that if I can get count of messages which have message.status=1 for each user (by this I meant to get count of unread messages) in the query, can I count multiple things in a single query?

Edited by manishk3008
Link to comment
Share on other sites

OK, here you go, this counts the status = 1 as well as the total number of messages. I reffered to it as unread because of what you were saying earlier, but I don't know why you would need a field for status for unread as well as a field for viewed. The key player, asside from the count(*) is the JOIN statement. It's one of the most powerfull tools you get when building queries, you should look into learning more about them if you have the time. Still, here's the code.

SELECT messages.global_ref_id,
messages.`to`,
messages.`from`,
messages.message,
messages.status,
messages.viewed,
events.event_time,
count_table.msg_count as `total Number of messages`,
get_unread.unread_count as `Number of unread messages`
FROM
messages
INNER JOIN
(select max(global_ref_id) as ref_id, count(*) msg_count from messages group by `from`)
as count_table
on (messages.global_ref_id = count_table.ref_id)
INNER JOIN events
on (events.global_id = messages.global_ref_id)

INNER JOIN
(SELECT MAX(global_ref_id) as unread_ref_id, count(*) as unread_count FROM messages WHERE status=1 GROUP BY `from`)
as get_unread
ON (get_unread.unread_ref_id = messages.global_ref_id)

where messages.`to` = 192
order by events.global_id DESC

Link to comment
Share on other sites

Glad I could help. SQL is a lot more powerfull that it looks on the surface. I wouold also like to say that, for the future, you should get a list of MySQL reserved words and either print it off or bookmark it. These are words that are generaly core commands sich as DATE that, while some front ends will let you use them to create column names in your tables, can cause no end of problems when trying to run queries if you use them outside of their reserved purpose. Good luck, and you know where to find us if you have any more questions.

Link to comment
Share on other sites

I only realised after I posted that I could actualy play with your fiddle (I can't be the only one thinking that sounds wrong...). Being able to use your tables and sample data I came up with this:

SELECT messages.global_ref_id,
messages.`to`,
messages.`from`,
messages.message,
messages.status,
messages.viewed,
events.event_time,
count_table.msg_count as `total Number of messages`
FROM
messages
INNER JOIN
(select max(global_ref_id) as ref_id, count(*) msg_count from messages group by `from`)
as count_table
on (messages.global_ref_id = count_table.ref_id)
INNER JOIN events
on (events.global_id = messages.global_ref_id)
where messages.`to` = 192
order by events.global_id DESC

 

It preserves the overall message count while only showing the most recent record activity. I hope that's what you are after.

 

 

I tested this query on original database, there is one problem that it shows only 2 results only no matter what should be the number of rows it should return.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.