rohithv
BAN USERCREATE TABLE `country` (
`country_id` int(11) NOT NULL auto_increment,
`country_name` varchar(64) NOT NULL,
PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `city` (
`country_id` int(11) NOT NULL,
`city_name` varchar(64) NOT NULL,
CONSTRAINT `city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
select co.*, ci.*
from country co left join city ci
on co.country_id = ci.country_id
where ci.country_id is null;
select co.*, ci.*, count(ci.city_name)
from country co left join city ci
on co.country_id = ci.country_id
group by co.country_id
having count(ci.city_name) < 3;
CREATE TABLE `country` (
`country_id` int(11) NOT NULL auto_increment,
`country_name` varchar(64) NOT NULL,
PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `city` (
`country_id` int(11) NOT NULL,
`city_name` varchar(64) NOT NULL,
CONSTRAINT `city_country` FOREIGN KEY (`country_id`) REFERENCES `user` (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
select co.*
from country co left join city ci
on co.country_id = ci.country_id
where co.country_id is null;
select co.country_id, co.country_name
from country co left join city ci
on co.country_id = ci.country_id
group by country_id
having count(ci.city_name < 3);
I am assuming you aren't allowed to use nested queries either. Hence.
- rohithv April 28, 2015