Solved

messages for profiles that I wrote first AND are waiting for a response.

Posted on 2013-11-15
9
377 Views
Last Modified: 2013-12-13
I want to see all the messages for profiles that I wrote first AND are waiting for a response.
I want to see all the messages (messages staff3 sent, messages 'big' sent) for profile_id 'big'.

abl: abl wrote first, staff3 wrote last
swa: swa wrote first, swa wrote last
dtw: dtw wrote first, dtw wrote last
gam: gam wrote first, gam wrote last
h1a: hla wrote first, staff3 wrote last
nyh: nyh wrote first, nyh wrote last
big: staff3 wrote first, big wrote last

CREATE TABLE `a_messages2` (
       `a_messages_id` INT (11) auto_increment  primary key
     , `profile_id` VARCHAR(20)
     , `sender` VARCHAR(20)
     , `message_id` BIGINT (20)
     , `this_user` VARCHAR(20)
     );



INSERT INTO a_messages2
          (`profile_id`, `sender`, `message_id`, `this_user`)
    VALUES
('abl', 'staff3', 3662615602, 'staff3'),
('abl', 'abl', 3660687080, 'staff3'),
('abl', 'staff3', 3658803944, 'staff3'),
('abl', 'staff3', 3649952930, 'staff3'),
('abl', 'staff3', 3644250249, 'staff3'),
('abl', 'staff3', 3614235515, 'staff3'),
('abl', 'abl', 3614140442, 'staff3'),
('abl', 'staff3', 3613984751, 'staff3'),
('abl', 'staff3', 3613936332, 'staff3'),
('abl', 'abl', 3613469574, 'staff3'),
('abl', 'staff3', 3612970481, 'staff3'),
('abl', 'abl', 3609276901, 'staff3'),
('abl', 'abl', 3609276896, 'staff3'),
('abl', 'staff3', 3866086756, 'staff3'),
('abl', 'staff3', 3866079064, 'staff3'),
('abl', 'abl', 3856964132, 'staff3'),
('abl', 'staff3', 3844779912, 'staff3'),
('abl', 'staff3', 3916948647, 'staff3'),
('abl', 'staff3', 3880367425, 'staff3'),
('abl', 'abl', 3977088222, 'staff3'),
('abl', 'staff3', 3974513295, 'staff3'),
('abl', 'staff3', 3973285282, 'staff3'),
('abl', 'abl', 3960907806, 'staff3'),
('abl', 'staff3', 4003664468, 'staff3'),
('abl', 'abl', 4002246444, 'staff3'),
('abl', 'staff3', 3996089944, 'staff3'),
('abl', 'staff3', 4024726432, 'staff3'),
('abl', 'abl', 4021516823, 'staff3'),
('abl', 'staff3', 4018957318, 'staff3'),
('abl', 'abl', 4050609080, 'staff3'),
('abl', 'staff3', 4052368867, 'staff3'),
('abl', 'abl', 4052424820, 'staff3'),
('abl', 'staff3', 4053183569, 'staff3'),
  
('swa','swa','4153199171','staff3'),
('swa','staff3','4150323049','staff3'),
('swa','swa','4146362776','staff3'),
('swa','staff3','4142884979','staff3'),
('swa','staff3','4142884976','staff3'),
('DTw', 'DTw', '3658976250', 'staff3'),
('DTw', 'DTw', '3658422687', 'staff3'),
('DTw', 'DTw', '3653399588', 'staff3'),
('DTw', 'DTw', '3653174764', 'staff3'),
('DTw', 'DTw', '3546098525', 'staff3'),
('DTw', 'DTw', '3545571594', 'staff3'),
('DTw', 'DTw', '3545571593', 'staff3'),
('DTw', 'DTw', '3540574071', 'staff3'),
('DTw', 'DTw', '3540454344', 'staff3'),
('DTw', 'DTw', '3540376883', 'staff3'),
('fre', 'fre', '3544889093', 'staff3'),
('gam', 'gam', '3955910171', 'staff3'),
('gam', 'gam', '3955910165', 'staff3'),
('gam', 'gam', '3649458031', 'staff3'),
('gam', 'gam', '3648816297', 'staff3'),
('gam', 'gam', '3648783076', 'staff3'),
('gam', 'gam', '3648516109', 'staff3'),
('gam', 'gam', '3647758123', 'staff3'),
('gam', 'gam', '3554774961', 'staff3'),
('gam', 'gam', '3540425395', 'staff3'),
('gam', 'gam', '3540357159', 'staff3'),
('gam', 'gam', '3540345288', 'staff3'),
('h1a', 'staff3', '3700557963', 'staff3'),
('h1a', 'h1a', '3693946789', 'staff3'),
('h1a', 'h1a', '3693806133', 'staff3'),
('h1a', 'staff3', '3684800156', 'staff3'),
('h1a', 'h1a', '3684173904', 'staff3'),
('h1a', 'h1a', '3682095987', 'staff3'),
('h1a', 'h1a', '3682095983', 'staff3'),
('h1a', 'h1a', '3542388954', 'staff3'),
('nyh', 'nyh', '3554817690', 'staff3'),
('nyh', 'nyh', '3554796334', 'staff3'),
('nyh', 'nyh', '3554701760', 'staff3'),
('nyh', 'nyh', '3554689575', 'staff3'),
('nyh', 'nyh', '3541014726', 'staff3'),
('nyh', 'nyh', '3541012663', 'staff3'),
('nyh', 'nyh', '3540619436', 'staff3'),
('nyh', 'nyh', '3540474531', 'staff3'),

('big', 'big', '3947506235', 'staff3'),
('big', 'staff3', '3947496223', 'staff3'),
('big', 'big', '3947489941', 'staff3'),
('big', 'staff3', '3938366539', 'staff3'),
('big', 'staff3', '3647107151', 'staff3'),
('big', 'big', '3587867444', 'staff3'),
('big', 'staff3', '3542393433', 'staff3')

Open in new window


this a query to see all the messages where the highest message_id is not sent by staff3
SELECT
         m2.profile_id
       , m2.sender
       , m2.message_id
       , m2.this_user
FROM (
      SELECT
              mx.sender
      FROM (
            SELECT
              profile_id
            , sender
            , max(message_id) AS message_id
            FROM a_messages2
            WHERE profile_id=sender
            GROUP BY
              profile_id
            , sender
           ) AS mx
      LEFT JOIN a_messages2 AS m ON mx.profile_id =  m.profile_id
                                AND mx.sender     <> m.sender
                                AND mx.message_id <  m.message_id
      WHERE m.a_messages_id IS NULL
	 ) AS mmx
INNER JOIN a_messages2 AS m2 ON mmx.sender = m2.sender
;

Open in new window


I am looking for the all the messages from a profile where lowest message_id='staff3' and highest message_id!='staff3'
From the mysql inserts, the only answer is profile_id 'big'
0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39655224
Is there a table of profile attributes?

Why don't you save yourself a lot of processing and store origin of profile?

I other questions you have differentiated between:
> first contact by staff
> first contact by non-staff

store this distinction
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39655319
If my understanding of this question is correct then you should be getting BOTH 'big' and 'swa' as results.
| A_MESSAGES_ID | PROFILE_ID | SENDER | MESSAGE_ID | THIS_USER |
|---------------|------------|--------|------------|-----------|
|            83 |        big | staff3 | 3542393433 |    staff3 | << started by
|            77 |        big |    big | 3947506235 |    staff3 | >> last message from

|            38 |        swa | staff3 | 4142884976 |    staff3 | << started by
|            34 |        swa |    swa | 4153199171 |    staff3 | >> last message from

Open in new window

The query I proposed here is:
SELECT
*
FROM a_messages2
WHERE profile_id IN (
                      SELECT
                      profile_id
                      FROM a_messages2
                      WHERE (
                               ( /* here we determine who originated the profile */
                                   message_id IN (SELECT min(message_id)
                                                  FROM a_messages2 GROUP BY profile_id)
                                AND
                                   profile_id <> sender /* originated by staff */
                               )
                            OR
                               ( /* here we determine who has sent latest message */
                                   message_id IN (SELECT max(message_id)
                                                  FROM a_messages2 GROUP BY profile_id)
                                AND
                                   profile_id = sender /* needs a response by staff */
                               )
                            )
                      GROUP BY profile_id
                      HAVING count(*) = 2
                      )
ORDER BY profile_id, message_id
;

Open in new window

0
 

Author Comment

by:rgb192
ID: 39655359
Why don't you save yourself a lot of processing and store origin of profile?

I other questions you have differentiated between:
> first contact by staff
> first contact by non-staff

Open in new window



Thank you for the suggestion, but that would be a larger php problem which may never have an answer.


using mysql workbench I am trying to run the whole query but query execution interrupted after 3 minutes
http://www.experts-exchange.com/Database/MySQL/Q_28296665.html


or I have tried only running the query in the middle without the larger query which also timeout

I also tried adding where 'staff3'

                      SELECT
                      profile_id
                      FROM a_messages2
                      WHERE (
                               ( /* here we determine who originated the profile */
                                   message_id IN (SELECT min(message_id)
                                                  FROM a_messages2 GROUP BY profile_id)
                                AND
                                   profile_id <> sender /* originated by staff */
                               )
                            OR
                               ( /* here we determine who has sent latest message */
                                   message_id IN (SELECT max(message_id)
                                                  FROM a_messages2 GROUP BY profile_id)
                                AND
                                   profile_id = sender /* needs a response by staff */
                               )
                            )
and this_user='staff3'
                      GROUP BY profile_id
                      HAVING count(*) = 2

Open in new window


There are numerous staff members
still does a timeout
even when I do another staff member that has  20 messages
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39655493
Efficiency is precisely why I suggested storing that decision. As your store of messages grows you will face an ever increasing issue determining who started what profile - each and every time you run these queries. Your data model lacks a date/time attributes as well.

How many records are currently in a_messages2 and what is the growth rate?
0
 

Author Closing Comment

by:rgb192
ID: 39656111
Question has been answered, Thank you .

when I do
create like
and
insert into where this_user='staff3'
subquery works

with a smaller user, the entire query works

I will open up another question about future problems.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39658144
Thanks.

When you open that new question I suggest you include all details of the table(s) involved
including indexes

Cheers, Paul
0
 

Author Comment

by:rgb192
ID: 39658152
Would indexes be the primary key auto increment column. How could i do insert with indexes
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39658313
By the nature of that question it appears you have much to catch-up on.

here's an introduction:
http://en.wikipedia.org/wiki/Database_index

The purpose of indexing is to speed-up queries.

You can have more than one index on any table. You can still do inserts into a table with more than one index.

However there is a "trade-off":
Indexes speed-up queries but can slow-down inserts/updates

The reason for slowing-down inserts is that each additional index is also maintained while inserting/updating. However, I think you would find that with your table additional indexes would be of substantial benefit.
0
 

Author Comment

by:rgb192
ID: 39717854
Indexing may speed up queries for me.

But the previous query you showed me failed (for me) because of the mysql_workbench query timeout.


I have two related question of which I think you can solve


indexing table:
http://www.experts-exchange.com/Database/MySQL/Q_28318003.html

another query that returns profile_id where staff has never solved a message:
http://www.experts-exchange.com/Database/MySQL/Q_28318002.html
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question