Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-11-08
4
Medium Priority
?
378 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
[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
  • 2
4 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39635150
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 49

Expert Comment

by:PortletPaul
ID: 39635152
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 111

Expert Comment

by:Ray Paseur
ID: 39635646
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
ID: 39651032
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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

715 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