LLLvbphb

1use tempdb
2go
3
4drop table if exists dbo.City;
5drop table if exists dbo.Country;
6drop table if exists dbo.Continent;
7
8create table dbo.Continent
9(
10	continent_id int not null primary key,
11	continent nvarchar(100) not null unique
12)
13
14create table dbo.Country
15(
16	country_id int not null primary key,
17	continent_id int not null references dbo.Continent(continent_id),
18	country nvarchar(100) not null unique
19)
20
21create table dbo.City
22(
23	city_id int not null primary key identity,
24	country_id int not null references dbo.Country(country_id),
25	city nvarchar(100) not null 
26)
27go
28
29insert into dbo.Continent (continent_id, continent) values
30(1, 'Africa'), 
31(2, 'Anctartica'), 
32(3, 'Asia'), 
33(4, 'Australia'), 
34(5, 'North America'), 
35(6, 'South America'), 
36(7, 'Europe')
37go
38
39insert into dbo.Country (country_id, continent_id, country)
40values 
41(1, 1, 'Nigeria'),
42(2, 1, 'South Africa'),
43(3, 1, 'Egypt'),
44(4, 1, 'Algeria'),
45(5, 1, 'Angola'),
46(6, 3, 'China'),
47(7, 3, 'India'),
48(8, 3, 'Japan')
49
50go
51
52insert into dbo.City(country_id, city)
53values 
54(1, 'Lagos'),
55(1, 'Abuja')
56go
57
58create or alter procedure dbo.GetContinentsDetail
59as
60select result = 
61(
62select 
63	continents.continent_id as ContinentId,
64	continents.continent as ContinentName,
65	countries.country_id as CountryId,
66	countries.country as CountryName,
67	cities.city_id as CityId,
68	cities.city as CityName	
69from 
70	dbo.Continent continents
71left join 
72	dbo.Country countries on continents.continent_id = countries.continent_id
73left join
74	dbo.City cities on countries.country_id = cities.country_id
75for json auto
76)
77go
78
79exec dbo.GetContinentsDetail
80go