Question

Table Structure

CREATE TABLE IF NOT EXISTS `blogs` (
  `id` int(11) NOT NULL auto_increment,
  `title` text collate utf8_bin NOT NULL,
  `content` longtext collate utf8_bin NOT NULL,
  `active` tinyint(4) NOT NULL default  0 ,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2768 ;

CREATE TABLE IF NOT EXISTS `pics` (
  `id` int(11) NOT NULL auto_increment,
  `blogid` int(11) NOT NULL default  0 ,
  `islogo` tinyint(4) NOT NULL default  0 ,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=4132 ;

CREATE TABLE IF NOT EXISTS `vdos` (
  `id` int(11) NOT NULL auto_increment,
  `blogid` int(11) NOT NULL default  0 ,
  `file` varchar(255) collate utf8_bin NOT NULL,
  `title` varchar(255) collate utf8_bin NOT NULL,
  `description` text collate utf8_bin NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=3759 ;

Query

select distinct b.id from blogs b 
left join pics p ON b.id = p.blogid 
left join vdos v ON b.id = v.blogid 
where p.islogo =  0  and b.`active` =  1

我打算做的是列出有照片或录像的博客。正在做的是,它只列出有照片的博客,而没有列出只有录像的博客。

谁能看到我做了什么错误?

Answer 1

The p.islogo is what s causes only blog with shows. 你们不得不这样做。

where p.islogo =  0  and b.`active` =  1  or p.islogo IS NULL

此外,在无照片的情况下配对博客。

Edit: Sorry initially misread the question. The where clause should probably be changed to

WHERE (p.islogo = "0" AND p.id IS NOT NULL) OR (v.id IS NOT NULL)

Answer 2

That s because you set the condition that pics.islogo is 0 . It can never be 0 for blogs without pictures. Move the condition to the join:

select distinct b.id from blogs b 
left join pics p ON b.id = p.blogid and p.islogo =  0 
left join vdos v ON b.id = v.blogid
where b.`active` =  1

Answer 3

select from blogs b 
left join pics p ON b.id = p.blogid 
left join vdos v ON b.id = v.blogid 
where p.islogo =  0  and b.`active` =  1  GROUP BY b.id;

Table Structure

Query

友情链接