mysql过滤结果与子查询结果

Question

i have two tables:

titles

• id
• title

entry

• id
• entry
• title_id
• user_id
• time (timestamp)

i need to check if any new entry is submitted after my last entry at that title. looking for a query which filters the updates (different from my user_id) on each title and list the result with a number included which shows the new entries.

---

Example

titles

id | title

1 | Cars
2 | Computers

entry

id | entry    | title_id | user_id | time
1  | bmw      | 1        | 1       | 1418595811
2  | mercedes | 2        | 2       | 1418595812
3  | ibm      | 2        | 2       | 1418595813
4  | hp       | 2        | 3       | 1418595814
5  | dell     | 2        | 1       | 1418595815
6  | acer     | 2        | 3       | 1418595816
7  | asus     | 2        | 4       | 1418595817
8  | toshiba  | 2        | 4       | 1418595818
9  | ferrari  | 1        | 3       | 1418595819
10 | toyota   | 1        | 4       | 1418595820
11 | honda    | 1        | 2       | 1418595821

Expected results:

if user_id=1 ->

Cars (3)
Computers (3)

if user_id=2 ->

Cars (0)
Computers (5)

if user_id=3 ->

Cars (2)
Computers (2)

as seen above, [user_id=1] submits a car (bmw). after that, other users submitted entries to that title. i need a query for new submissions for each title after user's last submission. (using the time field) only need is if there is new submission. if so, how many?

i need something like this:

select count(entry) 
from entry where time>last_entry_time[user_id] 
left join titles 
where title.id=entry.title_id

Answer 1

This does it for all users:

SELECT e1.user_id, t.title, COUNT(e2.id) AS count
FROM titles AS t
LEFT JOIN (SELECT user_id, title_id, MAX(time) AS last_entry
           FROM entry
           GROUP BY user_id, title_id) AS e1
ON t.id = e1.title_id
LEFT JOIN entry AS e2 ON e2.title_id = e1.title_id AND e2.user_id != e1.user_id AND e2.time > e1.last_entry
GROUP BY e1.user_id, t.id
ORDER BY e1.user_id, t.title

DEMO

To do it for a specific user:

SET @user_id = 1;

SELECT t.title, COUNT(e2.id) AS count
FROM titles AS t
LEFT JOIN (SELECT user_id, title_id, MAX(time) AS last_entry
           FROM entry
           WHERE user_id = @user_id
           GROUP BY title_id) AS e1
ON t.id = e1.title_id
LEFT JOIN entry AS e2 ON e2.title_id = e1.title_id AND e2.user_id != @user_id AND e2.time > e1.last_entry
GROUP BY t.id
ORDER BY t.title

DEMO