Solved

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

Posted on 2013-11-08
4
369 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 500 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 110

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

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…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

623 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