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
LVL 1
rgb192Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
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.
0
PortletPaulfreelancerCommented:
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?
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

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

ORDER BY
        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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
0
rgb192Author Commented:
this shows all the messages from other users and not sent by me

so the original question is answered
0
rgb192Author Commented:
I have a question improved by your answer:
http://www.experts-exchange.com/Database/MySQL/Q_28257474.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.