Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this query (SQLFiddle):

SELECT
c.name,
a.user_id,
a.status_id,
a.title,
a.rtime,
u.user_name,
s.status_name

FROM company c

LEFT JOIN action a ON a.company_id=c.id
LEFT JOIN user u ON u.id=a.user_id
LEFT JOIN status s ON s.id=a.status_id



WHERE u.user_name='Morgan'

-- WHERE c.name='Fiddle'

GROUP BY c.id

HAVING a.rtime IS NULL OR a.rtime = (
 SELECT max(rtime)
 FROM action a2
 WHERE deleted IS NULL
 AND a2.company_id = c.id
 )

Problem 1

I want to list all the companies, and show the user and status where they last made an action on the company. At the same time show the companies where no actions have been made.

Problem 2

I also need to be able to search for the user by name, thereby selecting all companies were this user had the last activity. The query is made from a form, so I can inject variables.


I am not able to change the database SCHEMA at the moment, but advices for a future migration is much appretiated.

I've tried binding it together with INNER JOIN ( SELECT.. ) t ON but I can't get my head around it.

I've also tried methods from here, here, and here but I can't get the person with the latest activity right.

MySQL version: 5.5.16. The company table has about 1mill rows, and the action table is at 70K, growing. Performance is important to me here.

How can this be solved?

share|improve this question
2  
See this question: MySQL optimized query Instead of MAX(Marks) per TaskID, you want MAX(ActivityDate) per Company.) There is not much difference if you have one table or a join. –  ypercube Jan 12 at 21:17
add comment

4 Answers

up vote 4 down vote accepted

Your query can be simplified to:

SELECT
    c.id,
    c.name,
    a.rtime,
    s.status_name,
    u.user_name
FROM company c
    LEFT JOIN
      ( SELECT 
            company_id,
            MAX(rtime) AS maxdate
        FROM action
        WHERE deleted IS NULL
        GROUP BY company_id
      ) AS x ON x.company_id = c.id
    LEFT JOIN action a ON  a.deleted IS NULL
                       AND a.company_id = x.company_id 
                       AND a.rtime = x.maxdate 
    LEFT JOIN user u ON u.id = a.user_id
    LEFT JOIN status s ON s.id = a.status_id
WHERE 
    c.name LIKE 'Company%' ;

An index on (deleted, company_id, rtime) would make the derived table subquery efficient. I suppose you already have indexes on the columns used for the joins and on the Company (name).

SQL-Fiddle

share|improve this answer
    
This is simpler, and seems about 0.3 seconds faster per 100 row than my answer. –  stiq Jan 14 at 8:27
add comment

After trying out the different answers, I got it working, but I found it slow in my setup.

ypercube pointed me to an answer which pointed to other similar questions about how MySQL is grouping results. This lead me to this resource where I finally understood what was going on.

The result being a query like this:

SELECT
c.id,
c.name,

a.rtime,

s.status_name,

u.user_name


FROM company c

LEFT JOIN (
    SELECT 
        a.company_id,
        a.rtime,
        a.user_id,
        a.status_id
        FROM
            (
            SELECT company_id,
 MAX(rtime) as maxdate
            FROM action
            WHERE deleted IS NULL
            GROUP BY company_id
            ) as x

        LEFT JOIN action a ON a.rtime=x.maxdate AND a.company_id=x.company_id

) as a ON a.company_id=c.id

LEFT JOIN user u ON u.id=a.user_id
LEFT JOIN status s ON s.id=a.status_id

WHERE (1)

-- Search by user
-- AND u.user_name LIKE 'Johnny'

-- Seach by company name
AND c.name LIKE 'Company%'

Here's a fiddle with the working query

share|improve this answer
add comment

You need to move user, action and status tables into a sub-query, like this:

SELECT
c.name,
a.user_id,
a.status_id,
a.title,
a.max_rtime,
a.user_name,
a.status_name

FROM company c

LEFT JOIN
(select company_id, user_id, 
 max(rtime) max_rtime, 
 title, status_id, s.status_name,
 u.user_name
 from action INNER JOIN
  status s ON s.id=action.status_id
  INNER JOIN user u ON u.id=action.user_id
 where action.deleted IS NULL
 group by company_id, user_id, status_id, s.status_name, title, u.user_name
) a ON a.company_id=c.id AND a.user_name='Morgan'


GROUP BY c.id

SQL Fiddle

share|improve this answer
add comment

You have to perform left join that fill nonexistent data with nulls:

SELECT * 
  FROM companies AS w
  LEFT JOIN 
  (
         SELECT *
           FROM users AS q
          WHERE q.access_time = MAX(q.access_time)
          ORDER BY q.company_id ASC, q.access_time DESC
          GROUP BY q.company_id
  ) AS z ON z.company_id = w.id
  ORDER BY z.date DESC;

Way more better and efficient to maintain seperate table of last registered activities.

share|improve this answer
2  
You can't use MAX() in a WHERE clause this way. –  ypercube Jan 12 at 18:15
2  
And ODRER BY goes after GROUP BY –  ypercube Jan 12 at 18:17
    
Please see the fiddle to understand my question. My user table doesn't have a time column, the action table does, so I need to join then all together. This is where I get stuck. –  stiq Jan 12 at 18:53
1  
Sorry, but this SQL script is just not working as written now. –  Marian Jan 14 at 11:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.