want to return a list of profile_id (which is username) of users that are waiting for me to respond to them

This is a follow up to
http://www.experts-exchange.com/Database/MySQL/Q_28257474.html



http://sqlfiddle.com/#!9/9ee48/3

CREATE TABLE `a_messages2` (
       `a_messages_id` INT (11) auto_increment  primary key
     , `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)
     );

CREATE TABLE `search` (
       `search_id` INT (11) auto_increment  primary key
     , `profile_id` VARCHAR(20) DEFAULT NULL
     , `wrong` TINYINT (4) DEFAULT NULL
     , `this_user` VARCHAR(20) DEFAULT NULL
     , `sent` TINYINT (4) DEFAULT NULL
     , `friend` TINYINT (4) DEFAULT NULL
     , `comment` VARCHAR(1000) DEFAULT NULL
     );

Open in new window


SELECT
        m.profile_id
      , m.sender
      , m.profile_id_count
      , m.message_text
      , m.conversation_id
      , m.dateAgo
      , m.message_id
      , m.message_read
      , s.comment
FROM (
        SELECT
                @row_num := IF(@prev_value = a_messages2.sender, @row_num + 1, 1) AS RowNumber
              , a_messages2.profile_id
              , a_messages2.sender
              , a_messages2.message_text
              , a_messages2.conversation_id
              , a_messages2.dateAgo
              , a_messages2.message_id
              , a_messages2.message_read
              , mc.profile_id_count /* THE DOT ERREOR WAS HERE - ooops */
              , @prev_value := a_messages2.sender AS partition_by
        FROM a_messages2
        CROSS JOIN (SELECT @row_num := 1 AS x, @prev_value := '' AS y) AS cj
        INNER JOIN (
                        SELECT
                                  profile_id
                                , COUNT(profile_id) AS profile_id_count
                        FROM a_messages2
                        GROUP BY profile_id
                        HAVING COUNT(profile_id) > 1 /* this replaces a previous where condition in the outer query */
                   ) AS mc
                        ON a_messages2.profile_id = mc.profile_id

        WHERE a_messages2.sender != a_messages2.this_user /* not sent by me condition moved to here */

        ORDER BY a_messages2.sender, a_messages2.message_id DESC
     ) AS m
LEFT JOIN search AS s
        ON m.profile_id = s.profile_id

WHERE m.RowNumber = 1 /* this should provide "the latest message - per sender " */

ORDER BY
        m.profile_id

      , m.sender /* this was changed from:   m.this_user   */


      /* , message_id DESC */
/* as there should only be one message per sender order in message_id is probably not needed now */
;

Open in new window


the query returns many messages in a conversation from a user
please email me back
I have been waiting, please email me
have you received my last email

I only want to return a list of profile_id (which is username) of users that are waiting for me to respond to them
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:
but which of those fields will tell the query that you have not responded?
e.g. how do you you decide that you have not responded? (please describe this)
0
rgb192Author Commented:
The reason why there is a 'this_user' column is because I have multiple accounts

if the highest 'message_id' 'sender' is not 'this_user' (me)
then the customer is waiting for a response to their last message(s)
0
PortletPaulfreelancerCommented:
Thanks.
As I have no data to work with and it seems I'm making assumptions about the data that are wrong. I could be more precise if I had some relevant data from both tables.

However, here are 2 tests. The objective is to see if it is the table `search` is affecting the logic.

Test 1: remove table `search` (to learn if this is the problem). Is this the correct list of unanswered messages?
SELECT
        m.profile_id
      , m.sender
      , m.profile_id_count
      , m.message_text
      , m.conversation_id
      , m.dateAgo
      , m.message_id
      , m.message_read
FROM (
        SELECT
                @row_num := IF(@prev_value = a_messages2.sender, @row_num + 1, 1) AS RowNumber
              , a_messages2.profile_id
              , a_messages2.sender
              , a_messages2.message_text
              , a_messages2.conversation_id
              , a_messages2.dateAgo
              , a_messages2.message_id
              , a_messages2.message_read
              , a_messages2.this_user
              , mc.profile_id_count
              , @prev_value := a_messages2.sender AS partition_by
        FROM a_messages2
        CROSS JOIN (SELECT @row_num := 1 AS x, @prev_value := '' AS y) AS cj
        INNER JOIN (
                        SELECT
                                  profile_id
                                , COUNT(profile_id) AS profile_id_count
                        FROM a_messages2
                        GROUP BY profile_id
                        HAVING COUNT(profile_id) > 1 /* this replaces a previous where condition in the outer query */
                   ) AS mc
                        ON a_messages2.profile_id = mc.profile_id

        WHERE a_messages2.sender != a_messages2.this_user /* not sent by me condition moved to here */

        ORDER BY a_messages2.sender, a_messages2.message_id DESC
     ) AS m

WHERE m.RowNumber = 1 /* this should provide "the latest message - per sender " */

ORDER BY
        m.profile_id

      , m.sender /* this was changed from:   m.this_user   */


      /* , message_id DESC */
/* as there should only be one message per sender order in message_id is probably not needed now */
;

Open in new window

Test 2: move table `search` into subquery. Is this the correct list of unanswered messages?
SELECT
        m.profile_id
      , m.sender
      , m.profile_id_count
      , m.message_text
      , m.conversation_id
      , m.dateAgo
      , m.message_id
      , m.message_read
      , m.comment
FROM (
        SELECT
                @row_num := IF(@prev_value = a_messages2.sender, @row_num + 1, 1) AS RowNumber
              , a_messages2.profile_id
              , a_messages2.sender
              , a_messages2.message_text
              , a_messages2.conversation_id
              , a_messages2.dateAgo
              , a_messages2.message_id
              , a_messages2.message_read
              , a_messages2.this_user
              , mc.profile_id_count
              , search.comment
              , @prev_value := a_messages2.sender AS partition_by
        FROM a_messages2
        CROSS JOIN (SELECT @row_num := 1 AS x, @prev_value := '' AS y) AS cj
        INNER JOIN (
                        SELECT
                                  profile_id
                                , COUNT(profile_id) AS profile_id_count
                        FROM a_messages2
                        GROUP BY profile_id
                        HAVING COUNT(profile_id) > 1 /* this replaces a previous where condition in the outer query */
                   ) AS mc
                        ON a_messages2.profile_id = mc.profile_id
        LEFT JOIN search
                         ON a_messages2.profile_id = search.profile_id
                        AND a_messages2.this_user = search.this_user

        WHERE a_messages2.sender != a_messages2.this_user /* not sent by me condition */

        ORDER BY a_messages2.sender, a_messages2.message_id DESC
     ) AS m

WHERE m.RowNumber = 1 /* this should provide "the latest message - per sender " */

ORDER BY
        m.profile_id

      , m.sender /* this was changed from:   m.this_user   */


      /* , message_id DESC */
/* as there should only be one message per sender order in message_id is probably not needed now */
;

Open in new window

both of the queries above have been checked for syntax at:
http://sqlfiddle.com/#!9/9ee48/10

but without data I cannot test for results.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

rgb192Author Commented:
same results for both queries

if a profile_id sends the last 1+ messages then I see a row
for example a profile_id may send 4 messages asking where I am.

I only want to see last row or just a list of profiles I need to respond to
no duplicate names
0
rgb192Author Commented:
sample data

profile_id   sender   message_id this_user
amy              amy          99                    staff1                  -> want only this row (or 'amy')
amy              amy          2                       staff1                  
amy              staff1         1                       staff1                -> this is the first message (i sent)
ann              ann         94                       staff1                  -> want only this row (or 'ann')
ann              ann          7                       staff1                  
ann              staff1         6                       staff1                -> this is the first message (i sent)
bob              staff1         14                       staff1                -> bob never responded
0
PortletPaulfreelancerCommented:
??
WHERE a_messages2.sender != a_messages2.this_user

this is already part of the existing query,
So I am interpreting the above table this way:
"raw data"
    profile_id   sender   message_id this_user
    amy          amy      99         staff1     -> want only this row (or 'amy')
    amy          amy      2          staff1                  
    amy          staff1   1          staff1     -> this is the first message (i sent) 
    ann          ann      94         staff1     -> want only this row (or 'ann')
    ann          ann      7          staff1                  
    ann          staff1   6          staff1     -> this is the first message (i sent) 
    bob          staff1   14         staff1     -> bob never responded

"existing query result?"
    profile_id   sender   message_id this_user
    amy          amy      99         staff1     -> want only this row (or 'amy')
    amy          amy      2          staff1                  
    ann          ann      94         staff1     -> want only this row (or 'ann')
    ann          ann      7          staff1                  

"expected result"
    profile_id   sender   message_id this_user
    amy          amy      99         staff1     -> want only this row (or 'amy')
    ann          ann      94         staff1     -> want only this row (or 'ann')

Open in new window

AND, you ONLY want the columns shown above (please confirm yes/no)
0
PortletPaulfreelancerCommented:
From the following sample data:
INSERT INTO a_messages2
    (`profile_id`, `sender`, `message_id`, `this_user`)
VALUES
    ('amy', 'amy', 99, 'staff1'),
    ('amy', 'amy', 2, 'staff1'),
    ('amy', 'staff1', 1, 'staff1'),
    ('ann', 'ann', 94, 'staff1'),
    ('ann', 'ann', 7, 'staff1'),
    ('ann', 'staff1', 6, 'staff1'),
    ('bob', 'staff1', 14, 'staff1')
;

Open in new window

This is the result:
| PROFILE_ID | SENDER | PROFILE_ID_COUNT | MESSAGE_TEXT | CONVERSATION_ID | DATEAGO | MESSAGE_ID | MESSAGE_READ |
|------------|--------|------------------|--------------|-----------------|---------|------------|--------------|
|        amy |    amy |                3 |       (null) |          (null) |  (null) |         99 |       (null) |
|        ann |    ann |                3 |       (null) |          (null) |  (null) |         94 |       (null) |

Open in new window

Using this query:
SELECT
        m.profile_id
      , m.sender
      , m.profile_id_count
      , m.message_text
      , m.conversation_id
      , m.dateAgo
      , m.message_id
      , m.message_read
FROM (
        SELECT
                @row_num := IF(@prev_value = a_messages2.sender, @row_num + 1, 1) AS RowNumber
              , a_messages2.profile_id
              , a_messages2.sender
              , a_messages2.message_text
              , a_messages2.conversation_id
              , a_messages2.dateAgo
              , a_messages2.message_id
              , a_messages2.message_read
              , a_messages2.this_user
              , mc.profile_id_count
              , @prev_value := a_messages2.sender AS partition_by
        FROM a_messages2
        CROSS JOIN (SELECT @row_num := 1 AS x, @prev_value := '' AS y) AS cj
        INNER JOIN (
                        SELECT
                                  profile_id
                                , COUNT(profile_id) AS profile_id_count
                        FROM a_messages2
                        GROUP BY profile_id
                        HAVING COUNT(profile_id) > 1 /* this replaces a previous where condition in the outer query */
                   ) AS mc
                        ON a_messages2.profile_id = mc.profile_id

        WHERE a_messages2.sender != a_messages2.this_user /* not sent by me condition moved to here */

        ORDER BY a_messages2.sender, a_messages2.message_id DESC
     ) AS m

WHERE m.RowNumber = 1 /* this should provide "the latest message - per sender " */

ORDER BY
        m.profile_id

      , m.sender /* this was changed from:   m.this_user   */


      /* , message_id DESC */
/* as there should only be one message per sender order in message_id is probably not needed now */
;

Open in new window

You may see this in operation at: http://sqlfiddle.com/#!9/507ae/3
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:
works for the example I provided
thank you.
0
PortletPaulfreelancerCommented:
thanks, does it work for the real data?
0
rgb192Author Commented:
your query returns duplicate profile_id for some rows

so these are the results

http://www.experts-exchange.com/Database/MySQL/Q_28269377.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
MySQL Server

From novice to tech pro — start learning today.