MySQL query inside query

Question

I'm pretty new to MySQL, so this probably is an easy question for experienced people, but I just can't figure this out. I need to get items from database where user has posted a comment, and there is newer comment by someone else.

My table has these collumns

app_id - id of the item

app_comment - comment blank if rate is present

app_rate - rating blank if comment is present

date - date of posting

device_id - unique id to identify each user.

I have written simple query to get each comment the user(device_id) has posted on.

temp table:
SELECT `app_id` FROM `appaction` WHERE `device_id` = '00000000-5a24-5a06-0000-00005c907706' AND `app_comment` <> ''

But can't figure out what to do next. Now I somehow need to get only those app_id from main table where somebody else(different device_id) has a newer post on each of these items

Update: Data example: http://www.sqlfiddle.com/#!2/8d986/1

Note: that this is just sample to get the idea how the table looks. In reality it has about 10 000 values inside.

for example: I'm giving out device_id to php script, and in the output I want all app_id where user has posted himself and there is newer comment after his.

Input sample: http://www.sqlfiddle.com/#!2/66258/1

Desired output is for input ffffffff-c565-9b4a-ffff-ffffc9d821fd

img/Honda/AccordPICS/4167946157_a5b77093bc_b.jpg

because there is newer post for this app_id by somebody else.

I added sample to get the idea how table looks like. I'll try to provide sample data and desired output for that table — Datenshi
– Datenshi, Commented Aug 29, 2013 at 9:11

Jim · Accepted Answer · 2013-08-29 11:07:01Z

1

If I understand correctly then app_id is a link to another table and not the id for appaction. In that case you can do something like:

SELECT ua.id AS user_item_id, oa.id AS other_item_id FROM appaction ua
INNER JOIN appaction oa ON (ua.app_id = oa.app_id AND oa.app_comment <> '' AND oa.date > ua.date)
WHERE 
    ua.device_id = '00000000-5a24-5a06-0000-00005c907706' 
    AND oa.device_id != '00000000-5a24-5a06-0000-00005c907706' 
    AND ua.app_comment <> ''

This gets the items for a device that have an item for the same app_id posted after the item. other_item_id is the id of the later action.

Edit: To get the output you need just change the above query to:

SELECT DISTINCT(ua.app_id) FROM appaction ua
INNER JOIN appaction oa ON (ua.app_id = oa.app_id AND oa.app_comment <> '' AND oa.date > ua.date)
WHERE 
    ua.device_id = 'ffffffff-c565-9b4a-ffff-ffffc9d821fd' 
    AND ua.app_comment <> ''

edited Aug 29, 2013 at 11:07

answered Aug 29, 2013 at 8:39

Jim

22.7k6 gold badges55 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Datenshi Over a year ago

I added table example, to get a better understanding of a problem

Datenshi Over a year ago

Worked just as expected.. I'll take my time and try to figure out this query to understand it. Thank your for your help !

Jim Over a year ago

@Datenshi No problem, let me know if I can explain the query better.

rakeshjain · Accepted Answer · 2013-08-29 18:26:43Z

0

I guess you are looking for all those apps whose latest post is from other than device id 00000000-5a24-5a06-0000-00005c907706. If that is true, try the below query

SELECT `app_id`, group_concat(device_id) as device_ids FROM `appaction` 
GROUP BY app_id ORDER BY date DESC 
HAVING SUBSTRING_INDEX(device_ids,",",1)!= '00000000-5a24-5a06-0000-00005c907706'

edited Aug 29, 2013 at 18:26

answered Aug 29, 2013 at 8:44

rakeshjain

1,76111 silver badges11 bronze badges

1 Comment

Datenshi Over a year ago

I'm looking for those app_id who has a post by "device_id" and there is a newer post by different device_id for the same app_id tried executing this query but it gets sql syntax error.

Collectives™ on Stack Overflow

MySQL query inside query

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related