Solved

return all the messages with profile returned

Posted on 2013-11-03
8
304 Views
Last Modified: 2013-11-08
http://www.experts-exchange.com/Database/MySQL/Q_28283780.html#a39619770
Currently this query returns one row of sender, message_id

| SENDER | MESSAGE_ID |
|--------|------------|
|    abl | 3609276896 |


I would like to see all the rows of messages with profile_id 'abl'

Because when I check results, I have to type select * from a_messages where profile_id='abl'
0
Comment
Question by:rgb192
  • 5
  • 3
8 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
"nest" the previous query as a subquery, join back to messages via sender (so all sender messages get included), order the results as you like
SELECT
*
FROM (
      SELECT
              m.sender
            , m.message_id
      FROM (
            SELECT
              profile_id
            , min(message_id) AS message_id
            FROM a_messages2
            GROUP BY
              profile_id
           ) AS mn
      INNER JOIN a_messages2 AS m ON mn.message_id =  m.message_id
      WHERE m.profile_id = m.sender
     ) AS CK
INNER JOIN a_messages2 AS m2 ON ck.sender =  m2.sender 
ORDER BY
        m2.profile_id
      , m2.message_id ASC 
;

Open in new window

0
 

Author Comment

by:rgb192
Comment Utility
Error Code: 1054. Unknown column 'ck.sender' in 'on clause'
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
? it works for me
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
unchanged query with result displayed
**Query 1**:

    SELECT
    *
    FROM (
          SELECT
                  m.sender
                , m.message_id
          FROM (
                SELECT
                  profile_id
                , min(message_id) AS message_id
                FROM a_messages2
                GROUP BY
                  profile_id
               ) AS mn
          INNER JOIN a_messages2 AS m ON mn.message_id =  m.message_id
          WHERE m.profile_id = m.sender
         ) AS CK
    INNER JOIN a_messages2 AS m2 ON ck.sender =  m2.sender 
    ORDER BY
            m2.profile_id
          , m2.message_id ASC 
    

**[Results][2]**:
    
    | SENDER | MESSAGE_ID | A_MESSAGES_ID | PROFILE_ID | THIS_USER |
    |--------|------------|---------------|------------|-----------|
    |    abl | 3609276896 |            13 |        abl |    staff3 |
    |    abl | 3609276896 |            12 |        abl |    staff3 |
    |    abl | 3609276896 |            10 |        abl |    staff3 |
    |    abl | 3609276896 |             7 |        abl |    staff3 |
    |    abl | 3609276896 |             2 |        abl |    staff3 |
    |    abl | 3609276896 |            16 |        abl |    staff3 |
    |    abl | 3609276896 |            23 |        abl |    staff3 |
    |    abl | 3609276896 |            20 |        abl |    staff3 |
    |    abl | 3609276896 |            25 |        abl |    staff3 |
    |    abl | 3609276896 |            28 |        abl |    staff3 |
    |    abl | 3609276896 |            30 |        abl |    staff3 |
    |    abl | 3609276896 |            32 |        abl |    staff3 |

Open in new window

see: http://sqlfiddle.com/#!9/8efe9/4
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
the "alias" CK is defined on line 19

it is then referenced in the next line "ON ck.sender..." see the ck?

perhaps try making the "alias" in lower case, so change line 19 to this:

         ) AS ck
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
by the way "ck" is my abbreviation for check

you can choose your own aliases, just change the ck to something you prefer
0
 

Author Closing Comment

by:rgb192
Comment Utility
lowercase ck works great.
Thanks
0
 

Author Comment

by:rgb192
Comment Utility
Thanks for your query.

I have a related query where I would want to see all the messages (the messages I sent and received-- not just received)


http://www.experts-exchange.com/Database/MySQL/Q_28288994.html
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now