Solved

'staff3' can be the sender (which I want the query to show)

Posted on 2013-11-08
4
331 Views
Last Modified: 2013-11-15
http://www.experts-exchange.com/Database/MySQL/Q_28284093.html

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'

And this query shows all the messages where 'abl' is the sender
I want to see the messages where
 'abl' is the profile_id
so
'abl' can be the sender (which the query currently shows)
AND
'staff3' can be the sender (which I want the query to show)


    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


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')

Open in new window

0
Comment
Question by:rgb192
  • 2
4 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
Hi again, you can shorten your development times by learning some SQL I think.

This request wants to modify a query so that a profile_id is the focus, so profile_id needs to be how you join the table (not sender as it is in the question, see line 18)

The adjusted query for this will be, I have noted the changes in lines 5 and 18:
SELECT
*
FROM (
      SELECT
              m.profile_id /* changed to profile_id */
            , 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.profile_id =  m2.profile_id  /* changed to profile_id */
ORDER BY
        m2.profile_id
      , m2.message_id ASC 
;

Open in new window

Also please note although I will sometimes use "select *" that does NOT mean you should (we use it here sometimes to be brief - but in real production quality code you should specify each column, e.g.

SELECT
         m2.profile_id
       , m2.sender
       , m2.message_id
       , m2.this_user
FROM (
...

The other thing you SHOULD be doing is keeping notes inside the queries, so you can easily understand them in the future. For example:
/*
   displays all messages for profiles that were initiated by non-staff
   NOV. 9 2013
   refer: http://www.experts-exchange.com/Database/MySQL/Q_28288994.html
*/

SELECT
         m2.profile_id
       , m2.sender
       , m2.message_id
       , m2.this_user
FROM (
      /* this inner subquery is locating who sent the first message within a profile */
      SELECT
              m.profile_id /* changed to profile_id */
            , m.message_id
      FROM (
            SELECT
              profile_id
            , min(message_id) AS message_id /* lowest message_id will be used in join */
            FROM a_messages2
            GROUP BY
              profile_id
           ) AS mn
      INNER JOIN a_messages2 AS m ON mn.message_id =  m.message_id /* join to lowest message_id = who sent first */
      WHERE m.profile_id = m.sender /* this condition allows only those messages NOT sent by staff as the first message */
     ) AS ck
INNER JOIN a_messages2 AS m2 ON ck.profile_id =  m2.profile_id  /* now we inspect all messages in profiles that were initiated by non-staff */
ORDER BY
        m2.profile_id
      , m2.message_id ASC 
;

Open in new window

see: http://sqlfiddle.com/#!9/7f5af/3
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
by the way, line 42 of the data inserts is missing a comma

('swa','staff3','4142884976','staff3')

should be:

('swa','staff3','4142884976','staff3'),

no action required here, but if repeating this in other questions it would be useful to fix then
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
No points for this, but beware of the word "this" as in "this_user" since "this" is a reserved keyword in many languages, including PHP.  If you use it wrongly, it will cause you a lot of unwanted debugging time!
0
 

Author Closing Comment

by:rgb192
Comment Utility
Thank you for your help.

>>Eventually you will need sufficient knowledge of  SQL
I am learning by modifying your queries.
For example I can now see messages initiated by staff by modifying your query.

I have a related question:
messages for profiles that I wrote first AND are waiting for a response.
http://www.experts-exchange.com/Database/MySQL/Q_28295129.html
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
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: …

771 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

9 Experts available now in Live!

Get 1:1 Help Now