sql query is returning the same values twice

Question

Hi There,

I have this sql query, and it should be returning two values, which is does but it returns each returned row twice, the sql looks like this,

SELECT * FROM `mailers` 
  LEFT JOIN `mailer_content` ON `mailers`.`id` = `mailer_content`.`mailer_id` 
  LEFT JOIN `mailer_images` ON `mailer_content`.`id` = `mailer_images`.`content_id` 
WHERE `mailers`.`id` = 26

The table structure for the tables I am query look like this,

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

--
-- Table structure for table `mailers`
--

CREATE TABLE `mailers` (
  `id` int(11) NOT NULL auto_increment,
  `mailer_title` varchar(150) NOT NULL,
  `mailer_header` varchar(60) NOT NULL,
  `mailer_type` enum('single','multi') NOT NULL,
  `introduction` varchar(80) NOT NULL,
  `status` enum('live','dead','draft') NOT NULL,
  `flag` enum('sent','unsent') NOT NULL,
  `date_mailer_created` int(11) NOT NULL,
  `date_mailer_updated` int(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ;

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

--
-- Table structure for table `mailer_content`
--

CREATE TABLE `mailer_content` (
  `id` int(11) NOT NULL auto_increment,
  `headline` varchar(320) NOT NULL,
  `content` text NOT NULL,
  `mailer_id` int(11) NOT NULL,
  `position` enum('left','right','centre') default NULL,
  `tab_1_name` varchar(25) default NULL,
  `tab_1_link` varchar(250) default NULL,
  `tab_2_name` varchar(25) default NULL,
  `tab_2_link` varchar(250) default NULL,
  `tab_3_name` varchar(25) default NULL,
  `tab_3_link` varchar(250) default NULL,
  `tab_4_name` varchar(25) default NULL,
  `tab_4_link` varchar(250) default NULL,
  `created_at` int(10) NOT NULL,
  `updated_at` int(10) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `mailer_id` (`mailer_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

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

--
-- Table structure for table `mailer_images`
--

CREATE TABLE `mailer_images` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(150) NOT NULL,
  `filename` varchar(150) NOT NULL,
  `mailer_id` int(11) NOT NULL,
  `content_id` int(11) default NULL,
  `date_created` int(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=49 ;

I am sure that is must be a problem with my sql I just do not know what the problem is

Answer 1

If you use SELECT DISTINCT SQL will not return dupplicated rows, if there are some.

SELECT DISTINCT * FROM `mailers` LEFT JOIN `mailer_content` ON `mailers`.`id` = `mailer_content`.`mailer_id` LEFT JOIN `mailer_images` ON `mailer_content`.`id` = `mailer_images`.`content_id` WHERE `mailers`.`id` = 26

Answer 2

It doesn't look like an SQL isse to me; I suspect this is more likely down to the data in your tables.

My guess is that there are two rows in mailer_content where mailers.id = 26 and then two rows (or possibly 1 and 3) in mailer_images for each of the mailer_contents.

How many rows do each of the following queries return?

SELECT * FROM `mailers`
WHERE `mailers`.`id` = 26  

SELECT * FROM `mailer_content`
WHERE `mailer_content`.`id` = 26

My guess is that the first returns 1 row (because it has a primary key on id) and that the second returns two rows.

That all may be fine but my guess is that the following query returns 4 records:

SELECT * FROM `mailer_content`
LEFT JOIN `mailer_images` ON `mailer_content`.`id` = `mailer_images`.`content_id`
WHERE `mailer_content`.`id` = 26

Because either each content has two images each OR one content has one image and the other has three.

Answer 3

Could you please post the output of these queries:

SELECT  COUNT(*)
FROM    mailer_content mc
WHERE   mc.mailer_id = 26

SELECT  COUNT(*)
FROM    mailer_content mc
JOIN    mailer_images mi
ON      mi.content_id = mc.id
WHERE   mc.mailer_id = 26

Answer 4

U can use group by smthng. It will delete the same records. but u can delete nonsame rows. Use smthng without same values in different rows in original table.