Jump to content


Photo

Help For Building This Query.

mysql

  • Please log in to reply
9 replies to this topic

#1 manishk3008

manishk3008

    Newbie

  • New Members
  • Pip
  • 6 posts

Posted 19 November 2012 - 02:54 PM

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

#2 Muddy_Funster

Muddy_Funster

    Advanced Member

  • Members
  • PipPipPip
  • 3,271 posts

Posted 19 November 2012 - 03:54 PM

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.

Please: "This doesn't work..." is not a question.  We're not the government: we don't have anything to view what your doing on your computer.  Help us help you by asking a good question.

 

"Things needed to ask a "good" question:

  • A description of the context of your issue (Optional but can be a huge help in certain circumstances)
  • What is the actual problem (Mandatory)
  • What does the code actually do (Mandatory)
  • What you think the code should do / What you want the code to do (Mandatory)
  • What things have you tried so far (Optional, but missing it out just wastes your time and ours)
  • The actual code as you are running it - minus any personal information like Database Login Credentials (Mandatory - don't just post pseudo, the vast majority of issues are syntax and not logic)
  • As much info about your development environment as you can give - even if it's just letting us know you are using a hosting provider instead of a local install (Optional, but some questions can not be answered without it.)

 

 

My SQL/PHP Blog


#3 Muddy_Funster

Muddy_Funster

    Advanced Member

  • Members
  • PipPipPip
  • 3,271 posts

Posted 19 November 2012 - 04:17 PM

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.

Please: "This doesn't work..." is not a question.  We're not the government: we don't have anything to view what your doing on your computer.  Help us help you by asking a good question.

 

"Things needed to ask a "good" question:

  • A description of the context of your issue (Optional but can be a huge help in certain circumstances)
  • What is the actual problem (Mandatory)
  • What does the code actually do (Mandatory)
  • What you think the code should do / What you want the code to do (Mandatory)
  • What things have you tried so far (Optional, but missing it out just wastes your time and ours)
  • The actual code as you are running it - minus any personal information like Database Login Credentials (Mandatory - don't just post pseudo, the vast majority of issues are syntax and not logic)
  • As much info about your development environment as you can give - even if it's just letting us know you are using a hosting provider instead of a local install (Optional, but some questions can not be answered without it.)

 

 

My SQL/PHP Blog


#4 manishk3008

manishk3008

    Newbie

  • New Members
  • Pip
  • 6 posts

Posted 19 November 2012 - 04:28 PM

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, 19 November 2012 - 04:30 PM.


#5 Muddy_Funster

Muddy_Funster

    Advanced Member

  • Members
  • PipPipPip
  • 3,271 posts

Posted 19 November 2012 - 04:45 PM

Sure you can, to be honest, there isn't a whole lot you can't do with SQL. Give me 10min to have a play with the query again and I'll get back to you (or someone better will have the answer before then...).

Please: "This doesn't work..." is not a question.  We're not the government: we don't have anything to view what your doing on your computer.  Help us help you by asking a good question.

 

"Things needed to ask a "good" question:

  • A description of the context of your issue (Optional but can be a huge help in certain circumstances)
  • What is the actual problem (Mandatory)
  • What does the code actually do (Mandatory)
  • What you think the code should do / What you want the code to do (Mandatory)
  • What things have you tried so far (Optional, but missing it out just wastes your time and ours)
  • The actual code as you are running it - minus any personal information like Database Login Credentials (Mandatory - don't just post pseudo, the vast majority of issues are syntax and not logic)
  • As much info about your development environment as you can give - even if it's just letting us know you are using a hosting provider instead of a local install (Optional, but some questions can not be answered without it.)

 

 

My SQL/PHP Blog


#6 Muddy_Funster

Muddy_Funster

    Advanced Member

  • Members
  • PipPipPip
  • 3,271 posts

Posted 19 November 2012 - 04:58 PM

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

Please: "This doesn't work..." is not a question.  We're not the government: we don't have anything to view what your doing on your computer.  Help us help you by asking a good question.

 

"Things needed to ask a "good" question:

  • A description of the context of your issue (Optional but can be a huge help in certain circumstances)
  • What is the actual problem (Mandatory)
  • What does the code actually do (Mandatory)
  • What you think the code should do / What you want the code to do (Mandatory)
  • What things have you tried so far (Optional, but missing it out just wastes your time and ours)
  • The actual code as you are running it - minus any personal information like Database Login Credentials (Mandatory - don't just post pseudo, the vast majority of issues are syntax and not logic)
  • As much info about your development environment as you can give - even if it's just letting us know you are using a hosting provider instead of a local install (Optional, but some questions can not be answered without it.)

 

 

My SQL/PHP Blog


#7 manishk3008

manishk3008

    Newbie

  • New Members
  • Pip
  • 6 posts

Posted 19 November 2012 - 06:08 PM

Thanks you build this one perfect too!!!
SQL is like super cool, I really need to do some home work before proceeding with my project.

#8 Muddy_Funster

Muddy_Funster

    Advanced Member

  • Members
  • PipPipPip
  • 3,271 posts

Posted 20 November 2012 - 08:42 AM

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.

Please: "This doesn't work..." is not a question.  We're not the government: we don't have anything to view what your doing on your computer.  Help us help you by asking a good question.

 

"Things needed to ask a "good" question:

  • A description of the context of your issue (Optional but can be a huge help in certain circumstances)
  • What is the actual problem (Mandatory)
  • What does the code actually do (Mandatory)
  • What you think the code should do / What you want the code to do (Mandatory)
  • What things have you tried so far (Optional, but missing it out just wastes your time and ours)
  • The actual code as you are running it - minus any personal information like Database Login Credentials (Mandatory - don't just post pseudo, the vast majority of issues are syntax and not logic)
  • As much info about your development environment as you can give - even if it's just letting us know you are using a hosting provider instead of a local install (Optional, but some questions can not be answered without it.)

 

 

My SQL/PHP Blog


#9 manishk3008

manishk3008

    Newbie

  • New Members
  • Pip
  • 6 posts

Posted 21 November 2012 - 08:42 PM

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.

#10 Muddy_Funster

Muddy_Funster

    Advanced Member

  • Members
  • PipPipPip
  • 3,271 posts

Posted 21 November 2012 - 11:58 PM

Then you'll need to let us see a more accurate dataset. if the structure is the same then it must be something to do with how the data is grouped. You did change the where messages.to = to the appropriate value yeah?

Please: "This doesn't work..." is not a question.  We're not the government: we don't have anything to view what your doing on your computer.  Help us help you by asking a good question.

 

"Things needed to ask a "good" question:

  • A description of the context of your issue (Optional but can be a huge help in certain circumstances)
  • What is the actual problem (Mandatory)
  • What does the code actually do (Mandatory)
  • What you think the code should do / What you want the code to do (Mandatory)
  • What things have you tried so far (Optional, but missing it out just wastes your time and ours)
  • The actual code as you are running it - minus any personal information like Database Login Credentials (Mandatory - don't just post pseudo, the vast majority of issues are syntax and not logic)
  • As much info about your development environment as you can give - even if it's just letting us know you are using a hosting provider instead of a local install (Optional, but some questions can not be answered without it.)

 

 

My SQL/PHP Blog





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users