only want to see results of conversations that I need to respond to

TABLE `a_messages2`
  `a_messages_id` int(11) NOT NULL auto_increment
  `conversation_id` bigint(20)
  `profile_id` varchar(20)
  `sender` varchar(20)
  `message_id` bigint(20)
  `message_text` varchar(1000)
  `dateAgo` varchar(20)
  `message_read` tinyint(4)
  `this_user` varchar(20)
  PRIMARY KEY  (`a_messages_id`)
  UNIQUE KEY `unique_message_id` (`message_id`)

this is a query of conversations sorted alphabetically by profile_id where the conversation is more than 1 message

SELECT m.profile_id, m.sender, mc.profile_id_count, m.message_text, m.conversation_id, m.dateAgo, m.message_id, m.message_read, s.comment  
FROM a_messages2 m
left join search s on m.profile_id=s.profile_id
      JOIN (
            SELECT profile_id, COUNT(profile_id) as profile_id_count 
            FROM a_messages2 
            GROUP BY profile_id) mc ON m.profile_id = mc.profile_id 
where mc.profile_id_count>1 
ORDER BY m.profile_id, m.this_user, message_id desc   

Open in new window

want to add to the query
where for highest message_id in the conversation_id I only want results
where sender!=this_user
so I only see messages that I need to respond to
messages that are not sent by me
Who is Participating?
PortletPaulConnect With a Mentor Commented:
by the way, there are some parts of the question that we would have to guess, such as "not sent by me" -- this requires an understanding of your data and how 'you' are identified in that data. Does this work?
      , m.sender
      , mc.profile_id_count
      , m.message_text
      , m.conversation_id
      , m.dateAgo
      , m.message_id
      , m.message_read
      , s.comment
FROM a_messages2 m
LEFT JOIN search s
        ON m.profile_id = s.profile_id
                , COUNT(profile_id) AS profile_id_count
        FROM a_messages2
        GROUP BY profile_id
        ON m.profile_id = mc.profile_id
WHERE mc.profile_id_count > 1

AND m.sender != m.this_user /* is this "not sent by me" ? I don't know your data */

        m.profile_id, m.this_user, message_id DESC

Open in new window

I still need to know if this is MySQL or Microsoft SQL Server
but in addition we may need sample data (for both tables) plus,
expected results from that data
Hi, your table DDL indicates you are using MySQL (not MS SQL Server)
is it MySQL?

btw: It makes a difference to what solutions are viable for you.
rgb192Author Commented:
I converted this data from sql server, but am using mysql now
sorry wrong zone, I am using mysql

m.sender != m.this_user works to show the messages that were not sent by me

but I am looking for the highest message_id in the conversation_id where m.sender != m.this_user
because I want to see the conversation_id that I need to respond to
rgb192Author Commented:
this shows all the messages from other users and not sent by me

so the original question is answered
rgb192Author Commented:
I have a question improved by your answer:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.