Karthikeyan Muthukumaran Karthikeyan Muthukumaran - 3 months ago 8
SQL Question

Retrive counts of two columns from two diffrent tables with third table using join query in SQL

I have 3 tables

1.COUNTRY

2.STATE
3.CITY

This is my country table with two column

CountryID Name

This is my State table

state table

this is my city table

city table

i want to retrive the Count of state and city according to the country table, using join query,

Answer

Skipping the fact that your question is not asked well - try this query, it should work for you:

WITH
tab_a AS (
SELECT c.countryid, COUNT (s.stateid) AS state_num
FROM country c
LEFT JOIN state s ON c.countryid = s.countryid
GROUP BY c.countryid
),
tab_b AS (
SELECT c.countryid, COUNT (cc.cityid) city_num
FROM country c
LEFT JOIN state s ON c.countryid = s.countryid
LEFT JOIN city cc ON s.stateid = cc.stateid
GROUP BY c.countryid
)
SELECT a.countryid,
       a.state_num,
       b.city_num
FROM tab_a a JOIN tab_b b ON a.countryid=b.countryid