CODEWITHSUNDEEP
mysql
chrishomer
chrishomer

Reputation: 4920

How can I write this MySQL query?

We have a system where users send in bags of goods to us.

I am trying figure out how to pull users with a calculated field for the date they sent their 3rd bag of goods. The state on the history table that would indicate this event is "processed". If I wanted to just get the received date for a particular bag I would write:

SELECT bags.created_at FROM history 
WHERE history.state='received' AND history.bag_id LIMIT 1

Here are our tables:

users
  id

bags
  user_id

history
  state
  bag_id
  created_at

The end goal is a select query that gives us user records and adds the "field" 3rd_bag_received_at

Any ideas?

Upvotes: 0

Views: 105

Answers (2)

Sepster
Sepster

Reputation: 4849

You could group by user_id, and then add a having clause to include only users with count(distinct bag_id) >= 3.

Then limit that to 3, and order by to get the latest date max(created_at)

Does that help?

Implementation would look something like this:

select 
 user.id, 
 max(history.created_at) as third_bag_received_at

from
(

 select 
   user.id, 
   max(history.created_at)

 from 
   users

   inner join bags
   on user.id = bags.user_id

   inner join history
   on bags.id = history.bag_id

 where history.state = 'processed'

 group by
   user.id

 having
   count(history.bag_id >= 3)

 limit 3

 order by 2 -- does this work in mySql? (It means "column 2" in MS SQL)
)

Limit 1

EDIT: How to address join/aggregation to other tables in a computed column:

Rather than adding this as a calculated column, I would probably create a view with whatever info you needed for this requirement, including the third_bag_received column based on this query.

But if you really did need to implement this as a calculated column on your table:

I'd recommend creating a user function that accepts your user.id as an input, and returns the value of third_bag_received_at (again, based on the logic of the above query).

Then call this function from your calculated column. Refer to @user10635's answer on this SO question.

Upvotes: 3

Sign Show
Sign Show

Reputation: 121

I'm just developing a little more Sepster answer to make it clear. The question is erratic as it doesn't say that the table bags has any field called bags.id, I had to make it up.

SELECT users.id, MAX(history.created_at) as 3rd_bag_received_at
FROM users LEFT JOIN bags ON (users.id=bags.user_id)
LEFT JOIN history ON(bags.id = history.bag_id)
WHERE history.state='received'
GROUP BY users.id
HAVING COUNT(history.bag_id) >= 3;

Upvotes: 0

Related Questions