通过循环 ID 从 3 个表获取数据

Question

我有一个具有此结构的 3 个表的数据库，

    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=48 ;

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=54 ;

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=92 ;

ALTER TABLE `mailer_content`
  ADD CONSTRAINT `mailer_content_ibfk_1` 
     FOREIGN KEY (`mailer_id`) 
     REFERENCES `mailers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

如何从 id 和 mailer 表为 47 的所有列中获取所有数据？我目前有这个sql，我目前有这个sql，但它没有返回正确的数据，

   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` = 47

Answer 1

您省略了 mailer_images 上的连接条件：

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`
                         AND `mailers`.`id` = `mailer_images`.`mailer_id` 
    WHERE `mailers`.`id` = 47

Answer 2

试试这个

   SELECT *
     FROM `mailers` 
LEFT JOIN `mailer_content` ON `mailers`.`id` = `mailer_content`.`mailer_id` 
LEFT JOIN `mailer_images` ON `mailer_images`.`mailer_id` = `mailer`.`id` 
    WHERE `mailers`.`id` = 47