Mysql Query – Set 2

We have 2 tables Country and City as shown below:
Country (countryid, countryname )
City (countryid, cityname )

Q1. How do you get the countries that has no cities?
Q2. How do you get the countries that has less than 3 cities and also make sure the countries with no cities also show up?

Solution:

 

Ans 1. Use left outer join to get all countries


SELECT co.countryname

FROM Country co LEFT OUTER JOIN City ci ON co.countryid = ci.countryid

WHERE ci.countryid IS NULL

 

Ans 2. Use left outer join with group by and count function  to get count of cities


SELECT  co.countryname

FROM Country co LEFT OUTER JOIN City ci ON co.countryid = ci.countryid

GROUP BY co.countryId, ci.countryname

HAVING COUNT(co.countryId) < 3

One Thought on “Mysql Query – Set 2

  1. Rani Mathur on August 26, 2014 at 1:50 am said:

    I think for the answer query where countryid is null clause is missing.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Post Navigation