shan2batman Posted September 23, 2018 Share Posted September 23, 2018 (edited) hi, i'm trying to fetch results from the following query manually in phpmyadmin but couldn't figure out how to fetch em as it outputs an empty result . The query actually fetches results two tables 1) updates table: which is where users post personal updates 2)group table : which is where group members post their updates in groups. the thing is mysql fetches a empty result but it should be bringing results from the tables as they have values in it. here is the db structure: -- -- Table structure for table `friends` -- CREATE TABLE `friends` ( `friend_id` int(255) NOT NULL, `friend_one` varchar(255) NOT NULL, `friend_two` varchar(255) NOT NULL, `friends_date_made` datetime NOT NULL, `accepted` enum('0','1') NOT NULL DEFAULT '0', `friend_one_uid` int(255) NOT NULL, `friend_two_uid` int(11) NOT NULL, `count` int(255) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- CREATE TABLE `gmembers` ( `gm_id` int(255) NOT NULL, `gname` varchar(100) NOT NULL, `mname` varchar(100) NOT NULL, `approved` enum('0','1') NOT NULL, `admin` int(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `gmembers` -- CREATE TABLE `groups` ( `g_id` int(255) NOT NULL, `name` varchar(100) NOT NULL, `creation` datetime NOT NULL, `logo` varchar(255) NOT NULL, `invrule` enum('0','1') NOT NULL, `creator` varchar(100) NOT NULL, `description` varchar(5000) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `groups` -- CREATE TABLE `group_photos` ( `g_p_id` int(11) NOT NULL, `avatar` varchar(2555) NOT NULL, `g_id` int(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `group_posts` -- CREATE TABLE `group_posts` ( `gp_id` int(255) NOT NULL, `pid` varchar(16) NOT NULL, `gname` varchar(100) NOT NULL, `author_gp` varchar(255) NOT NULL, `type` enum('0','1') NOT NULL, `title` varchar(500) NOT NULL, `data` varchar(10000) NOT NULL, `pdate` datetime NOT NULL, `vote_up` int(255) NOT NULL, `vote_down` int(255) NOT NULL, `user_id_g` int(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `group_posts` -- and finally here is the query: select friends.friend_one, friends.friend_two, user.uname, updates.update_id,updates.update_body,updates.time,updates.title,updates.vote_up,updates.vote_down,updates.user_id_u,updates.account_name,updates.author, groups.name,groups.creator,groups.logo,group_photos.g_p_id,group_photos.avatar, group_photos.g_id, group_posts.gp_id,group_posts.pid,group_posts.author_gp,group_posts.type, group_posts.title, group_posts.data, group_posts.pdate,group_posts.vote_up, group_posts.vote_down, group_posts.user_id_g from user inner join friends on friends.friend_one<>'aboutthecreator' and friends.friend_two<>'aboutthecreator' and accepted='1' inner join updates on updates.author='shan2batman' and updates.author=friends.friend_one AND updates.author=friends.friend_two inner join gmembers on gmembers.mname=user.uname inner join groups on groups.name=gmembers.gname and groups.name="MEP news" inner join group_posts on group_posts.gp_id=user.user_id inner join group_photos on group_posts.gname=groups.name where gmembers.mname=group_posts.author_gp and groups.name=group_posts.gname and user.uname='aboutthecreator' order by time desc limit 10 pl help me to fetch results by correcting my mistakes in the query. Edited September 23, 2018 by shan2batman Quote Link to comment Share on other sites More sharing options...
requinix Posted September 23, 2018 Share Posted September 23, 2018 Try in phpMyAdmin. Not rocket science. Either your query is invalid, or it's not matching any results, or your code for dealing with the results is wrong. Quote Link to comment Share on other sites More sharing options...
shan2batman Posted September 23, 2018 Author Share Posted September 23, 2018 i'm trying it in phpmyadmin only @requinix and i don't think the code for dealing with the results is wrong as i copied it from functional queries scattered throughout the folder Quote Link to comment Share on other sites More sharing options...
shan2batman Posted September 23, 2018 Author Share Posted September 23, 2018 (edited) here is my trimmed code for update select user.uname, friends.friend_one,friends.friend_two,updates.update_id,updates.update_body,updates.time,updates.title,updates.vote_up,updates.vote_down,updates.user_id_u,updates.account_name,updates.author, groups.name,groups.creator,groups.logo,group_photos.g_p_id,group_photos.avatar, group_photos.g_id, group_posts.gp_id,group_posts.pid,group_posts.author_gp,group_posts.type, group_posts.title, group_posts.data, group_posts.pdate,group_posts.vote_up, group_posts.vote_down, group_posts.user_id_g from user inner join friends on friends.friend_one=user.uname OR friends.friend_two=user.uname and accepted='1' inner join updates on updates.author=friends.friend_one and updates.author=friends.friend_two AND updates.author=user.uname inner join group_posts on group_posts.gp_id=user.user_id inner join gmembers on gmembers.mname=group_posts.author_gp inner join groups on groups.name=gmembers.gname inner join group_photos on group_posts.gname=groups.name and groups.name=group_posts.gname where user.uname='shan2batman'and groups.name="MEP news" order by time desc limit 10 i'll try to explain what i'm trying to do first i want to fetch friend_one and friend_two along with the session user uname value and fetch the results from updates table. second i'm trying to match the author names and group names stored in group_posts and fetch it along with the query. Edited September 23, 2018 by shan2batman Quote Link to comment Share on other sites More sharing options...
requinix Posted September 23, 2018 Share Posted September 23, 2018 Without seeing the data it will be nearly impossible for someone here to identify why you aren't coming up with results. What does an EXPLAIN of the query produce? Quote Link to comment Share on other sites More sharing options...
shan2batman Posted September 23, 2018 Author Share Posted September 23, 2018 it produces no results just empty rows. how can i share data with you to trouble shoot the problem???? @requinix Quote Link to comment Share on other sites More sharing options...
requinix Posted September 23, 2018 Share Posted September 23, 2018 Run the EXPLAIN and post it. Quote Link to comment Share on other sites More sharing options...
shan2batman Posted September 23, 2018 Author Share Posted September 23, 2018 (edited) here is the EXPLAIN output: @requinix id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE updates ALL NULL NULL NULL NULL 36 Using where; Using temporary; Using filesort 1 SIMPLE group_photos ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) 1 SIMPLE user ref PRIMARY,uname uname 52 const 1 Using where; Using index 1 SIMPLE group_posts eq_ref PRIMARY PRIMARY 4 project.user.user_id 1 Using where 1 SIMPLE friends ref friend_one friend_one 257 const 2 Using index condition; Using where 1 SIMPLE groups ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) 1 SIMPLE gmembers ALL NULL NULL NULL NULL 6 Using where; Using join buffer (incremental, BNL j Edited September 23, 2018 by shan2batman Quote Link to comment Share on other sites More sharing options...
requinix Posted September 23, 2018 Share Posted September 23, 2018 Do you see where it says "using temporary" and "using filesort"? All the NULLs for possible_keys and key? That's bad. Do you have any indexes on those tables? Quote Link to comment Share on other sites More sharing options...
shan2batman Posted September 23, 2018 Author Share Posted September 23, 2018 sorry @requinix i don't have any indexes in those tables Quote Link to comment Share on other sites More sharing options...
requinix Posted September 23, 2018 Share Posted September 23, 2018 Start looking into that. You need indexes. They make your queries much, much more effective. But even without indexes you should still get results. Look at the data in the user table. Find the row with the matching uname. Then look at another table, one of the ones you joined directly to the user table, and find the row with matching data. Keep going until you can't find the data. Keep track of the tables and conditions you're checking by copying the query and deleting parts of it as you go. Quote Link to comment Share on other sites More sharing options...
Barand Posted September 23, 2018 Share Posted September 23, 2018 I am guessing that, in your "friends" table, if A and B are friends then friend_one=A and friend_two=B. In your join ON clause you have Quote inner join updates on updates.author=friends.friend_one and updates.author=friends.friend_two AND updates.author=user.uname so you are looking for someone where user.uname is friends with himself. Perhaps you really wanted to look for inner join updates on (updates.author=friends.friend_one OR updates.author=friends.friend_two) AND updates.author=user.uname Alternatively inner join updates on updates.author IN (friends.friend_one, friends.friend_two) AND updates.author=user.uname Quote Link to comment Share on other sites More sharing options...
Barand Posted September 23, 2018 Share Posted September 23, 2018 5 hours ago, shan2batman said: inner join gmembers on gmembers.mname=group_posts.author_gp inner join groups on groups.name=gmembers.gname I have told this to you before. Items like group name should occur once in the database - in the group table. Joins should be made on the ids, not the names. You really need to normalize your data correctly. I predict there will come a time when, having continually ignored the advice that you seek on this site, that advice will stop coming. Quote Link to comment Share on other sites More sharing options...
shan2batman Posted September 23, 2018 Author Share Posted September 23, 2018 @Barand but there are no ids matching the group between diferrent columns. Quote Link to comment Share on other sites More sharing options...
shan2batman Posted September 23, 2018 Author Share Posted September 23, 2018 and i'm sorry for being too sloppy. Quote Link to comment Share on other sites More sharing options...
Barand Posted September 23, 2018 Share Posted September 23, 2018 13 minutes ago, shan2batman said: @Barand but there are no ids matching the group between diferrent columns. The point is - there should be. If you can match on name, you can match on id instead. Foreign keys should be ids, not names, it's far more efficient. Quote Link to comment Share on other sites More sharing options...
shan2batman Posted September 23, 2018 Author Share Posted September 23, 2018 i see Quote Link to comment Share on other sites More sharing options...
shan2batman Posted September 24, 2018 Author Share Posted September 24, 2018 (edited) finally i managed to fetch data from tables but the problem now is even after using distinct, groupby and having clauses the result is producing duplicate results its killing my brain @requinix can you point me where it is going wrong this is the updated query: select distinct g.* ,gp.*,up.*, u.uname,u.avatar,u.user_id from group_posts as g left join user as u on u.uname=g.author_gp left join updates as up on up.author=u.uname left join groups as gp on g.group_id=gp.g_id where g.gname='MEP news' and u.uname='shan2batman' GROUP by g.data,up.update_body having u.uname='shan2batman'and g.gname='MEP news' order by u.user_id desc here is the output: pid gname gp_id author_gp type title data pdate vote_up vote_down user_id_g group_id g_id name creation logo invrule creator description update_id update_body url time host vote_up vote_down title user_id_u account_name author type data uname avatar user_id 1 0 MEP news 11 shan2batman 0 ok lets see my first message #qwert 2016-08-12 20:17:21 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL 288 what kind of a life am i living?? 2016-06-15 22:01:09 0 0 hai 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 12 shan2batman 0 my second message welcome to chennai 2018-09-23 18:39:30 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL 288 what kind of a life am i living?? 2016-06-15 22:01:09 0 0 hai 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 13 shan2batman 0 see what can i do to create a perfect social network 2018-09-23 18:54:54 0 0 0 25 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 288 what kind of a life am i living?? 2016-06-15 22:01:09 0 0 hai 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 14 shan2batman 0 lets see hi this is a test msg 2018-09-24 09:27:14 0 0 0 25 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 288 what kind of a life am i living?? 2016-06-15 22:01:09 0 0 hai 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 15 shan2batman 0 group post this is a test for group post 2018-09-24 09:28:46 0 0 0 25 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 288 what kind of a life am i living?? 2016-06-15 22:01:09 0 0 hai 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 11 shan2batman 0 ok lets see my first message #qwert 2016-08-12 20:17:21 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL 289 man im having the best luck im going to be a billi... 2016-06-15 22:05:00 0 0 bye 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 12 shan2batman 0 my second message welcome to chennai 2018-09-23 18:39:30 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL 289 man im having the best luck im going to be a billi... 2016-06-15 22:05:00 0 0 bye 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 13 shan2batman 0 see what can i do to create a perfect social network 2018-09-23 18:54:54 0 0 0 25 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 289 man im having the best luck im going to be a billi... 2016-06-15 22:05:00 0 0 bye 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 14 shan2batman 0 lets see hi this is a test msg 2018-09-24 09:27:14 0 0 0 25 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 289 man im having the best luck im going to be a billi... 2016-06-15 22:05:00 0 0 bye 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 15 shan2batman 0 group post this is a test for group post 2018-09-24 09:28:46 0 0 0 25 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 289 man im having the best luck im going to be a billi... 2016-06-15 22:05:00 0 0 bye 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 11 shan2batman 0 ok lets see my first message #qwert 2016-08-12 20:17:21 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL 290 #qwert is it ready<br /><img src="ht... 2016-08-12 20:16:15 0 0 qwertyu 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 12 shan2batman 0 my second message welcome to chennai 2018-09-23 18:39:30 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL 290 #qwert is it ready<br /><img src="ht... 2016-08-12 20:16:15 0 0 qwertyu 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 13 shan2batman 0 see what can i do to create a perfect social network 2018-09-23 18:54:54 0 0 0 25 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 290 #qwert is it ready<br /><img src="ht... 2016-08-12 20:16:15 0 0 qwertyu 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 14 shan2batman 0 lets see hi this is a test msg 2018-09-24 09:27:14 0 0 0 25 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 290 #qwert is it ready<br /><img src="ht... 2016-08-12 20:16:15 0 0 qwertyu 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 15 shan2batman 0 group post this is a test for group post 2018-09-24 09:28:46 0 0 0 25 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 290 #qwert is it ready<br /><img src="ht... 2016-08-12 20:16:15 0 0 qwertyu 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 11 shan2batman 0 ok lets see my first message #qwert 2016-08-12 20:17:21 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL 291 <img src="tinymce/js/tinymce/plugins/emoti... 2016-08-12 20:18:27 0 0 poiuytr 127 shan2batman shan2batman a <br><b>Shared via .<br> shan2batman WedDec715012120167478.png 127 0 MEP news 12 shan2batman 0 my second message welcome to chennai 2018-09-23 18:39:30 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL 291 <img src="tinymce/js/tinymce/plugins/emoti... 2016-08-12 20:18:27 0 0 poiuytr 127 shan2batman shan2batman a <br><b>Shared via .<br> shan2batman WedDec715012120167478.png 127 0 MEP news 13 shan2batman 0 see what can i do to create a perfect social network 2018-09-23 18:54:54 0 0 0 25 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 291 <img src="tinymce/js/tinymce/plugins/emoti... 2016-08-12 20:18:27 0 0 poiuytr 127 shan2batman shan2batman a <br><b>Shared via .<br> shan2batman WedDec715012120167478.png 127 0 MEP news 14 shan2batman 0 lets see hi this is a test msg 2018-09-24 09:27:14 0 0 0 25 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 291 <img src="tinymce/js/tinymce/plugins/emoti... 2016-08-12 20:18:27 0 0 poiuytr 127 shan2batman shan2batman a <br><b>Shared via .<br> shan2batman WedDec715012120167478.png 127 0 MEP news 15 shan2batman 0 group post this is a test for group post 2018-09-24 09:28:46 0 0 0 25 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 291 <img src="tinymce/js/tinymce/plugins/emoti... 2016-08-12 20:18:27 0 0 poiuytr 127 shan2batman shan2batman a <br><b>Shared via .<br> shan2batman WedDec715012120167478.png 127 0 MEP news 11 shan2batman 0 ok lets see my first message #qwert 2016-08-12 20:17:21 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL 296 hello #qwert 2016-08-12 20:21:10 0 0 test message! 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 12 shan2batman 0 my second message welcome to chennai 2018-09-23 18:39:30 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL 296 hello #qwert 2016-08-12 20:21:10 0 0 test message! 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 13 shan2batman 0 see what can i do to create a perfect social network 2018-09-23 18:54:54 0 0 0 25 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 296 hello #qwert 2016-08-12 20:21:10 0 0 test message! 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 14 shan2batman 0 lets see hi this is a test msg 2018-09-24 09:27:14 0 0 0 25 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 296 hello #qwert 2016-08-12 20:21:10 0 0 test message! 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 0 MEP news 15 shan2batman 0 group post this is a test for group post 2018-09-24 09:28:46 0 0 0 25 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 296 hello #qwert 2016-08-12 20:21:10 0 0 test message! 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 1 2 | Show all | | Number of rows: 25 50 100 250 500 Filter rows: Sort by key: PRIMARY (ASC)PRIMARY (DESC)None Sorry if it is a bit too long Edited September 24, 2018 by shan2batman formatting Quote Link to comment Share on other sites More sharing options...
Barand Posted September 24, 2018 Share Posted September 24, 2018 Define "duplicate results" Quote Link to comment Share on other sites More sharing options...
shan2batman Posted September 24, 2018 Author Share Posted September 24, 2018 @Barand repeated values coming while fetching data from tables Quote Link to comment Share on other sites More sharing options...
Barand Posted September 24, 2018 Share Posted September 24, 2018 If you mean the group_Id values etc are repeated, then yes, there are several group_id #11 for example. But each of those has a different update_id so is not a duplicate. Quote Link to comment Share on other sites More sharing options...
shan2batman Posted September 24, 2018 Author Share Posted September 24, 2018 (edited) yes it is, but how do i correct the group_id values because it is the reason why i posted it in our forum @Barand Edited September 24, 2018 by shan2batman Quote Link to comment Share on other sites More sharing options...
shan2batman Posted September 24, 2018 Author Share Posted September 24, 2018 (edited) here is an update on the results: gp_id pid gname author_gp type title data pdate vote_up vote_down user_id_g group_id comment_id comment_body time_c os_id author_c type_c vote_up_c vote_down_c user_id_c gm_id gname mname approved admin g_id name creation logo invrule creator description update_id update_body url time host vote_up vote_down title user_id_u account_name author type data uname avatar user_id 1 16 0 MEP news shan2batman 0 how do i overcome the problems lets see how it works after i delete group posts 2018-09-24 11:55:44 0 0 0 25 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 288 what kind of a life am i living?? 2016-06-15 22:01:09 0 0 hai 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 18 16 MEP news shan2batman 1 this is a comment 2018-09-24 12:31:48 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 288 what kind of a life am i living?? 2016-06-15 22:01:09 0 0 hai 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 19 17 MEP news shan2batman 1 this is a second comment 2018-09-24 12:32:39 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 288 what kind of a life am i living?? 2016-06-15 22:01:09 0 0 hai 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 16 0 MEP news shan2batman 0 how do i overcome the problems lets see how it works after i delete group posts 2018-09-24 11:55:44 0 0 0 25 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 289 man im having the best luck im going to be a billi... 2016-06-15 22:05:00 0 0 bye 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 18 16 MEP news shan2batman 1 this is a comment 2018-09-24 12:31:48 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 289 man im having the best luck im going to be a billi... 2016-06-15 22:05:00 0 0 bye 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 19 17 MEP news shan2batman 1 this is a second comment 2018-09-24 12:32:39 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 289 man im having the best luck im going to be a billi... 2016-06-15 22:05:00 0 0 bye 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 16 0 MEP news shan2batman 0 how do i overcome the problems lets see how it works after i delete group posts 2018-09-24 11:55:44 0 0 0 25 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 290 #qwert is it ready<br /><img src="ht... 2016-08-12 20:16:15 0 0 qwertyu 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 18 16 MEP news shan2batman 1 this is a comment 2018-09-24 12:31:48 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 290 #qwert is it ready<br /><img src="ht... 2016-08-12 20:16:15 0 0 qwertyu 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 19 17 MEP news shan2batman 1 this is a second comment 2018-09-24 12:32:39 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 290 #qwert is it ready<br /><img src="ht... 2016-08-12 20:16:15 0 0 qwertyu 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 16 0 MEP news shan2batman 0 how do i overcome the problems lets see how it works after i delete group posts 2018-09-24 11:55:44 0 0 0 25 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 291 <img src="tinymce/js/tinymce/plugins/emoti... 2016-08-12 20:18:27 0 0 poiuytr 127 shan2batman shan2batman a <br><b>Shared via .<br> shan2batman WedDec715012120167478.png 127 18 16 MEP news shan2batman 1 this is a comment 2018-09-24 12:31:48 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 291 <img src="tinymce/js/tinymce/plugins/emoti... 2016-08-12 20:18:27 0 0 poiuytr 127 shan2batman shan2batman a <br><b>Shared via .<br> shan2batman WedDec715012120167478.png 127 19 17 MEP news shan2batman 1 this is a second comment 2018-09-24 12:32:39 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 291 <img src="tinymce/js/tinymce/plugins/emoti... 2016-08-12 20:18:27 0 0 poiuytr 127 shan2batman shan2batman a <br><b>Shared via .<br> shan2batman WedDec715012120167478.png 127 16 0 MEP news shan2batman 0 how do i overcome the problems lets see how it works after i delete group posts 2018-09-24 11:55:44 0 0 0 25 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 296 hello #qwert 2016-08-12 20:21:10 0 0 test message! 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 18 16 MEP news shan2batman 1 this is a comment 2018-09-24 12:31:48 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 296 hello #qwert 2016-08-12 20:21:10 0 0 test message! 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 19 17 MEP news shan2batman 1 this is a second comment 2018-09-24 12:32:39 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 296 hello #qwert 2016-08-12 20:21:10 0 0 test message! 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 16 0 MEP news shan2batman 0 how do i overcome the problems lets see how it works after i delete group posts 2018-09-24 11:55:44 0 0 0 25 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 297 Hi, @aboutthecreator i welcome you to myexpresspad 2016-09-25 16:53:01 0 0 lets test this message 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 18 16 MEP news shan2batman 1 this is a comment 2018-09-24 12:31:48 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 297 Hi, @aboutthecreator i welcome you to myexpresspad 2016-09-25 16:53:01 0 0 lets test this message 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 19 17 MEP news shan2batman 1 this is a second comment 2018-09-24 12:32:39 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 297 Hi, @aboutthecreator i welcome you to myexpresspad 2016-09-25 16:53:01 0 0 lets test this message 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 16 0 MEP news shan2batman 0 how do i overcome the problems lets see how it works after i delete group posts 2018-09-24 11:55:44 0 0 0 25 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 299 what to expect from <a class="twitter-hashtag pret... 2016-11-04 03:54:27 0 0 my first post 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 18 16 MEP news shan2batman 1 this is a comment 2018-09-24 12:31:48 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 299 what to expect from <a class="twitter-hashtag pret... 2016-11-04 03:54:27 0 0 my first post 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 19 17 MEP news shan2batman 1 this is a second comment 2018-09-24 12:32:39 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 299 what to expect from <a class="twitter-hashtag pret... 2016-11-04 03:54:27 0 0 my first post 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 16 0 MEP news shan2batman 0 how do i overcome the problems lets see how it works after i delete group posts 2018-09-24 11:55:44 0 0 0 25 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 304 i'm on cloud nine 2016-12-07 10:22:14 0 0 oooh yeah 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 18 16 MEP news shan2batman 1 this is a comment 2018-09-24 12:31:48 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 304 i'm on cloud nine 2016-12-07 10:22:14 0 0 oooh yeah 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 here is the query: select distinct g.* ,cu.*,gm.*,gp.*,up.*, u.uname,u.avatar,u.user_id from group_posts as g left join user as u on u.uname=g.author_gp left join updates as up on up.author=u.uname left join groups as gp on g.group_id=gp.g_id left join gmembers as gm on gm.gname=g.gname inner join comment_update as cu on cu.author_c=up.author where g.gname='MEP news' and u.uname='shan2batman' GROUP by g.data,up.update_body having u.uname='shan2batman'and g.gname='MEP news' order by u.user_id desc Edited September 24, 2018 by shan2batman Quote Link to comment Share on other sites More sharing options...
Barand Posted September 24, 2018 Share Posted September 24, 2018 I don't know. The query is functioning correctly. I cannot advise how you should "correct" it without knowing exactly what your data is and the function of this query is. If the only thing that is different is the update_id then don't select that column. In fact you shouldn't be using "select star". Specify the columns you need. If the update_ids are different but other columns from the update table are the same, you have another normalization problem. Quote Link to comment Share on other sites More sharing options...
shan2batman Posted September 24, 2018 Author Share Posted September 24, 2018 1 hour ago, shan2batman said: here is an update on the results: gp_id pid gname author_gp type title data pdate vote_up vote_down user_id_g group_id comment_id comment_body time_c os_id author_c type_c vote_up_c vote_down_c user_id_c gm_id gname mname approved admin g_id name creation logo invrule creator description update_id update_body url time host vote_up vote_down title user_id_u account_name author type data uname avatar user_id 1 16 0 MEP news shan2batman 0 how do i overcome the problems lets see how it works after i delete group posts 2018-09-24 11:55:44 0 0 0 25 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 288 what kind of a life am i living?? 2016-06-15 22:01:09 0 0 hai 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 18 16 MEP news shan2batman 1 this is a comment 2018-09-24 12:31:48 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 288 what kind of a life am i living?? 2016-06-15 22:01:09 0 0 hai 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 19 17 MEP news shan2batman 1 this is a second comment 2018-09-24 12:32:39 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 288 what kind of a life am i living?? 2016-06-15 22:01:09 0 0 hai 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 16 0 MEP news shan2batman 0 how do i overcome the problems lets see how it works after i delete group posts 2018-09-24 11:55:44 0 0 0 25 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 289 man im having the best luck im going to be a billi... 2016-06-15 22:05:00 0 0 bye 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 18 16 MEP news shan2batman 1 this is a comment 2018-09-24 12:31:48 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 289 man im having the best luck im going to be a billi... 2016-06-15 22:05:00 0 0 bye 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 19 17 MEP news shan2batman 1 this is a second comment 2018-09-24 12:32:39 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 289 man im having the best luck im going to be a billi... 2016-06-15 22:05:00 0 0 bye 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 16 0 MEP news shan2batman 0 how do i overcome the problems lets see how it works after i delete group posts 2018-09-24 11:55:44 0 0 0 25 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 290 #qwert is it ready<br /><img src="ht... 2016-08-12 20:16:15 0 0 qwertyu 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 18 16 MEP news shan2batman 1 this is a comment 2018-09-24 12:31:48 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 290 #qwert is it ready<br /><img src="ht... 2016-08-12 20:16:15 0 0 qwertyu 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 19 17 MEP news shan2batman 1 this is a second comment 2018-09-24 12:32:39 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 290 #qwert is it ready<br /><img src="ht... 2016-08-12 20:16:15 0 0 qwertyu 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 16 0 MEP news shan2batman 0 how do i overcome the problems lets see how it works after i delete group posts 2018-09-24 11:55:44 0 0 0 25 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 291 <img src="tinymce/js/tinymce/plugins/emoti... 2016-08-12 20:18:27 0 0 poiuytr 127 shan2batman shan2batman a <br><b>Shared via .<br> shan2batman WedDec715012120167478.png 127 18 16 MEP news shan2batman 1 this is a comment 2018-09-24 12:31:48 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 291 <img src="tinymce/js/tinymce/plugins/emoti... 2016-08-12 20:18:27 0 0 poiuytr 127 shan2batman shan2batman a <br><b>Shared via .<br> shan2batman WedDec715012120167478.png 127 19 17 MEP news shan2batman 1 this is a second comment 2018-09-24 12:32:39 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 291 <img src="tinymce/js/tinymce/plugins/emoti... 2016-08-12 20:18:27 0 0 poiuytr 127 shan2batman shan2batman a <br><b>Shared via .<br> shan2batman WedDec715012120167478.png 127 16 0 MEP news shan2batman 0 how do i overcome the problems lets see how it works after i delete group posts 2018-09-24 11:55:44 0 0 0 25 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 296 hello #qwert 2016-08-12 20:21:10 0 0 test message! 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 18 16 MEP news shan2batman 1 this is a comment 2018-09-24 12:31:48 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 296 hello #qwert 2016-08-12 20:21:10 0 0 test message! 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 19 17 MEP news shan2batman 1 this is a second comment 2018-09-24 12:32:39 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 296 hello #qwert 2016-08-12 20:21:10 0 0 test message! 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 16 0 MEP news shan2batman 0 how do i overcome the problems lets see how it works after i delete group posts 2018-09-24 11:55:44 0 0 0 25 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 297 Hi, @aboutthecreator i welcome you to myexpresspad 2016-09-25 16:53:01 0 0 lets test this message 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 18 16 MEP news shan2batman 1 this is a comment 2018-09-24 12:31:48 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 297 Hi, @aboutthecreator i welcome you to myexpresspad 2016-09-25 16:53:01 0 0 lets test this message 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 19 17 MEP news shan2batman 1 this is a second comment 2018-09-24 12:32:39 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 297 Hi, @aboutthecreator i welcome you to myexpresspad 2016-09-25 16:53:01 0 0 lets test this message 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 16 0 MEP news shan2batman 0 how do i overcome the problems lets see how it works after i delete group posts 2018-09-24 11:55:44 0 0 0 25 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 299 what to expect from <a class="twitter-hashtag pret... 2016-11-04 03:54:27 0 0 my first post 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 18 16 MEP news shan2batman 1 this is a comment 2018-09-24 12:31:48 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 299 what to expect from <a class="twitter-hashtag pret... 2016-11-04 03:54:27 0 0 my first post 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 19 17 MEP news shan2batman 1 this is a second comment 2018-09-24 12:32:39 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 299 what to expect from <a class="twitter-hashtag pret... 2016-11-04 03:54:27 0 0 my first post 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 16 0 MEP news shan2batman 0 how do i overcome the problems lets see how it works after i delete group posts 2018-09-24 11:55:44 0 0 0 25 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 25 MEP news 2016-06-25 06:06:41 TueDec619191620165366.png 1 aboutthecreator MEP is a social network that aims to be like faceb... 304 i'm on cloud nine 2016-12-07 10:22:14 0 0 oooh yeah 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 18 16 MEP news shan2batman 1 this is a comment 2018-09-24 12:31:48 0 0 0 0 2 this is a profile comment 2018-09-24 12:36:21 302 shan2batman b 0 0 127 1 MEP news aboutthecreator 1 1 NULL NULL NULL NULL NULL NULL NULL 304 i'm on cloud nine 2016-12-07 10:22:14 0 0 oooh yeah 127 shan2batman shan2batman a shan2batman WedDec715012120167478.png 127 here is the query: select distinct g.* ,cu.*,gm.*,gp.*,up.*, u.uname,u.avatar,u.user_id from group_posts as g left join user as u on u.uname=g.author_gp left join updates as up on up.author=u.uname left join groups as gp on g.group_id=gp.g_id left join gmembers as gm on gm.gname=g.gname inner join comment_update as cu on cu.author_c=up.author where g.gname='MEP news' and u.uname='shan2batman' GROUP by g.data,up.update_body having u.uname='shan2batman'and g.gname='MEP news' order by u.user_id desc @Barand r u there bro Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.