manishk3008

Query:- select t . *, events.event_time as last_time from events, ( ( select bonding.type, bonding.global_id2 as target_post, bonding.target_id as on_whose_post, GROUP_CONCAT(bonding.shooter_id) as shooter_ids, GROUP_CONCAT(bonding.what_global_id) as shooted_what, MAX(bonding.what_global_id) as last, 'bonding' as flag from bonding where bonding.type = 1 and bonding.shooter_id in (select `user2` from relation_table where `user1` = 192) group by bonding.global_id2 ) union ( select bonding.type, bonding.global_id2 as target_post, bonding.target_id as on_whose_post, GROUP_CONCAT(bonding.shooter_id) as shooter_ids, GROUP_CONCAT(bonding.what_global_id) as shooted_what, MAX(bonding.what_global_id) as last, 'bonding' as flag from bonding where bonding.type = 2 and bonding.shooter_id in (select `user2` from relation_table where `user1` = 192) group by bonding.global_id2 ) union ( select bonding.type, bonding.global_id2 as target_post, bonding.target_id as on_whose_post, GROUP_CONCAT(bonding.shooter_id) as shooter_ids, GROUP_CONCAT(bonding.what_global_id) as shooted_what, MAX(bonding.what_global_id) as last, 'bonding' as flag from bonding where bonding.type = 5 and bonding.shooter_id in (select `user2` from relation_table where `user1` = 192) group by bonding.global_id2 ) union ( select bonding.type, bonding.global_id2 as target_post, bonding.target_id as on_whose_post, GROUP_CONCAT(bonding.shooter_id) as shooter_ids, GROUP_CONCAT(bonding.what_global_id) as shooted_what, MAX(bonding.what_global_id) as last, 'bonding' as flag from bonding where bonding.type = 9 and bonding.shooter_id in (select `user2` from relation_table where `user1` = 192) group by bonding.global_id2 ) union ( select bonding.type, bonding.global_id2 as target_post, bonding.target_id as on_whose_post, GROUP_CONCAT(bonding.shooter_id) as shooter_ids, GROUP_CONCAT(bonding.what_global_id) as shooted_what, MAX(bonding.what_global_id) as last, 'bonding' as flag from bonding where bonding.type = 10 and bonding.shooter_id in (select `user2` from relation_table where `user1` = 192) group by bonding.global_id2 ) )as t where events.global_id = t1.last databse structure is given here EVENTS- `global_id` int(11) NOT NULL AUTO_INCREMENT, `event_creator` int(11) NOT NULL, `event_type` int(2) NOT NULL, `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`global_id`) bonding:- `shooter_id` int(12) NOT NULL COMMENT 'id of person who did something', `type` int(4) NOT NULL COMMENT '1:news share; 2:photo share; 5:comment; 10:red flag; 9:green flag', `what_global_id` int(12) NOT NULL COMMENT 'global id of event which is done', `global_id2` int(12) NOT NULL COMMENT 'global id on which it is done', `target_id` int(12) NOT NULL

I have these two queries select t . *, events.event_time as last_time from events, ( ( select bonding.type, bonding.global_id2 as target_post, bonding.target_id as on_whose_post, GROUP_CONCAT(bonding.shooter_id) as shooter_ids, GROUP_CONCAT(bonding.what_global_id) as shooted_what, MAX(bonding.what_global_id) as last, 'bonding' as flag from bonding where bonding.type = 1 and bonding.shooter_id in (select `user2` from relation_table where `user1` = 192) group by bonding.global_id2 ) union ( select bonding.type, bonding.global_id2 as target_post, bonding.target_id as on_whose_post, GROUP_CONCAT(bonding.shooter_id) as shooter_ids, GROUP_CONCAT(bonding.what_global_id) as shooted_what, MAX(bonding.what_global_id) as last, 'bonding' as flag from bonding where bonding.type = 2 and bonding.shooter_id in (select `user2` from relation_table where `user1` = 192) group by bonding.global_id2 ) union ( select bonding.type, bonding.global_id2 as target_post, bonding.target_id as on_whose_post, GROUP_CONCAT(bonding.shooter_id) as shooter_ids, GROUP_CONCAT(bonding.what_global_id) as shooted_what, MAX(bonding.what_global_id) as last, 'bonding' as flag from bonding where bonding.type = 5 and bonding.shooter_id in (select `user2` from relation_table where `user1` = 192) group by bonding.global_id2 ) union ( select bonding.type, bonding.global_id2 as target_post, bonding.target_id as on_whose_post, GROUP_CONCAT(bonding.shooter_id) as shooter_ids, GROUP_CONCAT(bonding.what_global_id) as shooted_what, MAX(bonding.what_global_id) as last, 'bonding' as flag from bonding where bonding.type = 9 and bonding.shooter_id in (select `user2` from relation_table where `user1` = 192) group by bonding.global_id2 ) union ( select bonding.type, bonding.global_id2 as target_post, bonding.target_id as on_whose_post, GROUP_CONCAT(bonding.shooter_id) as shooter_ids, GROUP_CONCAT(bonding.what_global_id) as shooted_what, MAX(bonding.what_global_id) as last, 'bonding' as flag from bonding where bonding.type = 10 and bonding.shooter_id in (select `user2` from relation_table where `user1` = 192) group by bonding.global_id2 ) )as t and other one :- SELECT post_stream.type, post_stream.ref_global_id as target_post, post_stream.user_id as on_whose_post, post_stream.user_id as shooter_ids, post_stream.ref_global_id as shooted_what, post_stream.ref_global_id as last, 'stream' as flag, events.event_time as last_time FROM post_stream, events, relation_table WHERE events.global_id = post_stream.ref_global_id and post_stream.type IN (2 , 3, 7, AND post_stream.user_id = relation_table.user2 AND relation_table.user1 = 192 now I need to perform a join on both the queries to get combined result, but it is giving Every derived table must have its own alias error, where I should put an alias for derived table these two queries are running with no errors when run separately.

I tested this query on original database, there is one problem that it shows only 2 results only no matter what should be the number of rows it should return.

Thanks you build this one perfect too!!! SQL is like super cool, I really need to do some home work before proceeding with my project.

That worked like charm!!! There is one more thing I would like to add to query, that if I can get count of messages which have message.status=1 for each user (by this I meant to get count of unread messages) in the query, can I count multiple things in a single query?

I have this database table named messages: global_ref_id int(12) to int(12) from int(12) message text status int(1) viewed int(1) where global_ref_id is id of messages (foreign key from table events), to is user id of user who received message, from is user id of user who send the message. I want to generate a query which gives me result displaying latest message from each user to a user (say whose user_id is 192) also giving total number of messages from each user to user 192. Till now I came up with this query to get results: SELECT messages.*,events.event_time, COUNT(messages.from) as "Total number of Messages to User" FROM messages, events WHERE events.global_id = messages.global_ref_id AND messages.to = 192 GROUP BY messages.from ORDER BY events.event_time DESC now problem is that, this query is displaying the oldest message instead of latest message what I need is Latest message. Using MYSql version:5.5.25a For easy understanding I have created table in sql fiddle: http://sqlfiddle.com/#!2/c68dc/7

Hi, I started developing php like 3 months ago, and seriously loving this language. Currently working on few project based on php.