Solved

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

Posted on 2013-11-08
4
339 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
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 48

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 108

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 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

22 Experts available now in Live!

Get 1:1 Help Now