Jump to content
shan2batman

couldn't figure out how to fetch values in this query need help

Recommended Posts

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 by shan2batman

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by shan2batman

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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 by shan2batman

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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 Descending 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&lt;br /&gt;&lt;img src=&quot;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&lt;br /&gt;&lt;img src=&quot;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&lt;br /&gt;&lt;img src=&quot;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&lt;br /&gt;&lt;img src=&quot;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&lt;br /&gt;&lt;img src=&quot;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 &lt;img src=&quot;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 &lt;img src=&quot;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 &lt;img src=&quot;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 &lt;img src=&quot;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 &lt;img src=&quot;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 by shan2batman
formatting

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by shan2batman

Share this post


Link to post
Share on other sites

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 Descending 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&lt;br /&gt;&lt;img src=&quot;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&lt;br /&gt;&lt;img src=&quot;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&lt;br /&gt;&lt;img src=&quot;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 &lt;img src=&quot;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 &lt;img src=&quot;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 &lt;img src=&quot;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 by shan2batman

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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 Descending 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&lt;br /&gt;&lt;img src=&quot;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&lt;br /&gt;&lt;img src=&quot;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&lt;br /&gt;&lt;img src=&quot;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 &lt;img src=&quot;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 &lt;img src=&quot;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 &lt;img src=&quot;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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.