CODEWITHSUNDEEP
databasemysql
HyderA
HyderA

Reputation: 21391

MySQL Query not selecting correctly from 2 join tables

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.

Can anyone see what I am doing wrong?

Upvotes: 1

Views: 124

Answers (3)

pooja
pooja

Reputation: 2432

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;

Upvotes: -1

Jim
Jim

Reputation: 22656

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

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 (p.islogo = "0" AND p.id IS NOT NULL) OR (v.id IS NOT NULL)

Upvotes: 1

GolezTrol
GolezTrol

Reputation: 116130

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'

Upvotes: 3

Related Questions