Doble left outer join SQL Server

[critina]

Buenas tardes,

Podría alguien ayudarme? Mi problema es el siguiente, tengo tres tablas:

Tabla 1 CONTINENTE
CONTINENTE_ID CONTINENTE
1 AMERICA
2 AFRICA
3 ASIA



TABLA 2 PAIS
PAIS_ID CONTINENTE PAIS
1 AMERICA COLOMBIA
2 AMERICA CUBA
3 AFRICA KENYA

Como veis no tengo pais para ASIA

TABLA 3 CIUDAD

CIUDAD_ID CONTINENTE PAIS CIUDAD
1 AMERICA COLOMBIA BOGOTA
2 AMERICA COLOMBIA CARTAGENA
3 AMERICA CUBA LA HABANA
4 ASIA NULL PEKIN

La idea de la query es la siguiente, necesito para cada pais el número de ciudades que tiene, si no tiene ciudades me devuelve cero.

El resultado sería:

AMERICA COLOMBIA 2
AMERICA CUBA 1
ASIA NULL 1
AFRICA KENYA 0

Lo he intentado de la siguiente manera pero no me sale bien:

SELECT B.CONTINENTE, B.PAIS, COUNT(A.PAIS)
FROM CONTINENTE B LEFT OUTER JOIN PAIS C
ON B.CONTINENTE=C.CONTINENTE
LEFT OUTER JOIN CIUTDAD A ON B.CONTINENTE=A.CONTINENTE AND C.PAIS=A.PAIS
GROUP BY B.CONTINENTE, B.PAIS


Muchas gracias

[Libras]

Aqui hay varias cosas, primero tus tablas no estan normalizadas, repites los nombres de los continentes y paises en las tablas de paises y ciudades, mejor usa id's para que quede normalizada, segundo pides el total de ciudades por pais, si asia tiene una ciudad que es pekin pero no tiene un pais como quieres que salga 1, a tu logica seria el numero de ciudades por continente no por pais, ya que si es por pais no tiene porque salir pekin, si es por continente si.

Código SQL:

CREATE TABLE #continente(
id INT,
continente VARCHAR(20)
)
 
INSERT INTO #continente VALUES (1,'America')
INSERT INTO #continente VALUES (2,'Africa')
INSERT INTO #continente VALUES (3,'Asia')
CREATE TABLE #pais(
id INT,
id_continente INT,
pais VARCHAR(20)
)
INSERT INTO #pais VALUES (1,1,'colombia')
INSERT INTO #pais VALUES (2,1,'cuba')
INSERT INTO #pais VALUES (3,2,'kenia')
 
 
CREATE TABLE #ciudad(
id INT,
id_continente INT,
id_pais INT,
nombre VARCHAR(20)
)
 
INSERT INTO #ciudad VALUES (1,1,1,'bogota')
INSERT INTO #ciudad VALUES (2,1,1,'cartagena')
INSERT INTO #ciudad VALUES (3,1,2,'la habana')
INSERT INTO #ciudad VALUES (3,3,NULL,'Pekin')
 
SELECT continente,pais, COUNT(ci.id) AS ciudades FROM #continente AS C
LEFT JOIN #pais AS p ON (c.id=p.id_continente)
LEFT JOIN #ciudad AS ci ON (ci.id_pais=p.id AND ci.id_continente=c.id)
GROUP BY continente,pais
 

Saludos!

[critina]

Muchísimas gracias,

Ahora funciona perfectamente

[Libras]

de nada :)