2个连接表中没有正确选择MySQL查询

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'

What I intend to do is to list blog ids that have pictures or videos. What this query is doing is that it only lists blogs that have pictures, and does not list blog ids that have only a video.

我想做的是列出有图片或视频的博客id。这个查询只列出有图片的博客，而不列出只有视频的博客id。

Can anyone see what I am doing wrong?

有人能看出我做错了什么吗?

3 个解决方案

#1

The p.islogo is what's causing only blog with pictures. You'll have to do

p。islogo是导致博客只有图片的原因。你必须做的

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

To also match blogs without pictures.

也可以匹配没有图片的博客。

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

编辑:很抱歉一开始读错了问题。where子句应该改为

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

#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:

那是因为你设置了图片的条件。islogo是“0”。没有图片的博客永远不会是0。将条件移动到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'

#3

-1

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;

#1