Getting duplicate posts from a joined mysql query

[gsshanker10]

i’m trying to print posts in my php script to display posts like facebook timeline but the problem is i get duplicate posts. how do i stop printing duplicate posts and bring original posts.
here is the query:

select distinct gp.gp_id as gp1,gp.pid,gp.author_gp,gp.gname,gp.type as ty1,gp.title as tit1,gp.data as dat1,gp.pdate,gp.group_id,gp.author_id,
         u.avatar,u.user_id ,
            up.update_id as up1,up.update_body,up.time,up.title as tit2,up.account_name,up.author,up.type as ty2,up.data as dat2 
            from group_posts as gp
           join user as u on u.uname=gp.author_gp 
            join updates as up on u.uname=up.author
            where 
            gp.gname='MEP news' 
            and up.update_id >391  and up.author in("shan2batman", "aboutthecreator") order by time,pdate desc limit 0,5

[r937]

i don’t see any duplications at all

in fact your DISTINCT is not necessary, and it’s expensive

here’s your query, somewhat reformatted for readability

SELECT DISTINCT 
       gp.gp_id AS gp1
     , gp.pid
     , gp.author_gp
     , gp.gname
     , gp.type AS ty1
     , gp.title AS tit1
     , gp.data AS dat1
     , gp.pdate
     , gp.group_id
     , gp.author_id
     , u.avatar
     , u.user_id 
     , up.update_id AS up1
     , up.update_body
     , up.time
     , up.title AS tit2
     , up.account_name
     , up.author
     , up.type AS ty2
     , up.data AS dat2 
  FROM group_posts AS gp
  JOIN user AS u 
    ON u.uname = gp.author_gp 
  JOIN updates AS up 
    ON up.author = u.uname
   AND up.update_id > 391
   AND up.author IN ('shan2batman','aboutthecreator')
 WHERE gp.gname='MEP news' 
ORDER 
    BY uip.time
     , gp.pdate DESC LIMIT 0,5

[gsshanker10]

@r937 but i get it from the updates table bro see it in the screenshot

[r937]

i saw the screenshot bro, that’s why i said there were no duplicates

[gsshanker10]

but what abt the update_id column and its table sir, it displays only one post, when there is more than 20. @r937

[r937]

well, maybe you joined it incorrectly – we don’t know your table relationships, the PKs and FKs

we don’t know your table data contents, either – perhaps you loaded your test data incorrectly

[gsshanker10]

thats why i’m here as i don’t know how to solve them sir can u help @r937

[r937]

@gsshanker10

sir can u help @r937

sure… please start by identifying all the PKs and FKs in your tables

[gsshanker10]

in the first table updates update_id is the primary key, user_id_u is a foreign key with a relationship tip to user tables user_id.
In the second table group_posts the gp_id is a primary key, pid is a foreign key to gp_id with a relationship, the group_id key is foreign key with relationship to groups tables g_id, finally the author_id is a foreign key with relationship to users tables user_id.
you can check it in the screenshots.

[r937]

i’m sorry, i cannot read those screenshots

(when you post only one, i can click on it to show larger resolution, but when there’s more than one, clicking on one simply goes to the next or previous image)

but please don’t post images, post the text results of SHOW CREATE TABLE tablename statements

[Mittineague]

Maybe unrelated to the problem. @gsshanker10 I have a strong suspicion but I haven’t used phpMyAdmin for years, so I don’t know. In the first post, why is phpMyAdmin underlining the “time” in the ORDER BY ?

Please run a clean SELECT (no WHERE) on the table to see if any rows have the same value in the “time” column. (a sane LIMIT might be a good idea)

If there are I think it would be wise to check how the values are getting there during INSERT / UPDATE rather than figure out a more complicated SELECT to work with bad data.

[gsshanker10]

@Mittineague i’ll try after answering @r937

[gsshanker10]

@r937 i’m more of a phpmyadmin kind of guy so here is a copy from those tables dump as i created it in a few clicks rather than by hand in the commmandline. Let me know if it helps.
Group_posts sql DUMP:-

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,
  `group_id` int(255) NOT NULL,
  `author_id` int(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `group_posts`
  ADD PRIMARY KEY (`gp_id`),
  ADD KEY `group_id` (`group_id`),
  ADD KEY `author_id` (`author_id`),
  ADD KEY `group_id_2` (`group_id`,`author_id`),
  ADD KEY `pid` (`pid`),
  ADD KEY `gp_id` (`gp_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `group_posts`
--
ALTER TABLE `group_posts`
  MODIFY `gp_id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=105;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `group_posts`
--
ALTER TABLE `group_posts`
  ADD CONSTRAINT `group_posts_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`g_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `group_posts_user_id` FOREIGN KEY (`author_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

updates table DUMP:-

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `project`
--

-- --------------------------------------------------------

--
-- Table structure for table `updates`
--

CREATE TABLE `updates` (
  `update_id` int(255) NOT NULL,
  `update_body` varchar(10000) NOT NULL,
  `url` varchar(100) NOT NULL,
  `time` datetime NOT NULL,
  `host` varchar(100) NOT NULL,
  `vote_up` int(255) NOT NULL,
  `vote_down` int(255) NOT NULL,
  `title` varchar(1000) NOT NULL,
  `user_id_u` int(255) NOT NULL,
  `account_name` varchar(255) NOT NULL,
  `author` varchar(255) NOT NULL,
  `type` enum('a','b','c') NOT NULL,
  `data` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for table `updates`
--
ALTER TABLE `updates`
  ADD PRIMARY KEY (`update_id`),
  ADD KEY `user_id_fk_upd` (`user_id_u`),
  ADD KEY `user_id_fk_upd_2` (`user_id_u`),
  ADD KEY `user_id_u` (`user_id_u`),
  ADD KEY `user_id_u_2` (`user_id_u`),
  ADD KEY `update_id` (`update_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `updates`
--
ALTER TABLE `updates`
  MODIFY `update_id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=399;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `updates`
--
ALTER TABLE `updates`
  ADD CONSTRAINT `updates_ibfk_1` FOREIGN KEY (`user_id_u`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

[r937]

you’ve got table group_posts, with author_id as FK referencing user

then you’ve got table updates, with user_id_u as FK referencing user

so if a group post can have only one author, but that author can have many updates, that might explain why each post is showing multiple times in your query results

helps?

[r937]

by the way, many of your indexes are redundant (and therefore inefficient)

change this –

ALTER TABLE `updates`
  ADD PRIMARY KEY (`update_id`),
  ADD KEY `user_id_fk_upd` (`user_id_u`),
  ADD KEY `user_id_fk_upd_2` (`user_id_u`),
  ADD KEY `user_id_u` (`user_id_u`),
  ADD KEY `user_id_u_2` (`user_id_u`),
  ADD KEY `update_id` (`update_id`);

to this –

ALTER TABLE `updates`
  ADD PRIMARY KEY (`update_id`),
  ADD KEY `user_id_fk_upd` (`user_id_u`)

the ones i removed are redundant

[gsshanker10]

@r937 when i try to execute the query in phpmyadmin i get the following error how to rectify it a simple google search didn’t enlighten me.

# Error

**SQL query:**

``

ALTER TABLE `updates`
  ADD PRIMARY KEY (`update_id`),
  ADD KEY `user_id_fk_upd` (`user_id_u`)

`#1068 - Multiple primary key defined`

[r937]

#1068 - Multiple primary key defined

you’re trying to re-add stuff that’s already there

[gsshanker10]

@r937 i don’t know how to solve it. can you enligten me by giving it in steps

[m_hutley]

Drop the existing keys before trying to add the new definitions of those keys

ALTER TABLE `updates` DROP PRIMARY KEY, DROP KEY ...

[gsshanker10]

@r937 i did as you asked and am still getting duplicate reults pl take a look at the following output from the latest execution of the query.

gp1	pid	author_gp	gname	ty1	tit1	dat1	pdate	group_id	author_id	up1	update_body	time	tit2	account_name	author	ty2	dat2
91	0	shan2batman	MEP news	0	question	Lorem ipsum dolor sit amet, consectetur adipiscing…	2018-11-11 18:44:42	25	127	288	what kind of a life am i living??	2016-06-15 22:01:09	hai	shan2batman	0	a
90	0	shan2batman	MEP news	0	title	Lorem ipsum dolor sit amet,sed diam nonumy eirmod …	2018-10-28 00:17:17	25	127	288	what kind of a life am i living??	2016-06-15 22:01:09	hai	shan2batman	0	a
88	0	shan2batman	MEP news	0	piuytrcfvgh	Lorem ipsum dolor sit amet,sed diam nonumy eirmod …	2018-10-28 00:01:41	25	127	288	what kind of a life am i living??	2016-06-15 22:01:09	hai	shan2batman	0	a
87	0	shan2batman	MEP news	0	poiuytrdcfvgbhnjm	Lorem ipsum dolor sit amet,sed diam nonumy eirmod …	2018-10-28 00:01:16	25	127	288	what kind of a life am i living??	2016-06-15 22:01:09	hai	shan2batman	0	a
86	0	shan2batman	MEP news	0	poiuytrdcfvgbhnjm	Lorem ipsum dolor sit amet,sed diam nonumy eirmod …	2018-10-27 23:59:17	25	127	288	what kind of a life am i living??	2016-06-15 22:01:09	hai	shan2batman	0	a