clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798673 fiddles created (41906 in the last week).

create table #Schedules ( ScheduleId int, ScheduleName varchar(20) ); create table #ScheduleThings ( ScheduleThingId int, ScheduleId int, Thing decimal(18,2)); create table #ScheduleOtherThings ( ScheduleOtherThingId int, ScheduleId int, OtherThing varchar(50)); insert into #Schedules ( ScheduleId, ScheduleName ) values (1, 'A'), (2, 'B'); insert into #ScheduleThings ( ScheduleThingId, ScheduleId, Thing ) values (1, 1, 10.22), (2, 1, 11.02), (3, 1, 11.89), (1, 2, 19.23), (2, 2, 20.04), (3, 2, 20.76), (4, 2, 21.37); insert into #ScheduleOtherThings ( ScheduleOtherThingId, ScheduleId, OtherThing ) values (1, 1, 'Always'), (2, 1, 'Sometimes'), (3, 2, 'Seldom'), (4, 2, 'Always'), (5, 2, 'Never');
14 rows affected
 hidden batch(es)


select fr.result from ( select s.ScheduleId as [schedules.schedule_id], s.ScheduleName as [schedules.schedule_name], ( SELECT ScheduleThingId, Thing FROM #ScheduleThings WHERE ScheduleId = s.ScheduleId AND ScheduleThingId < 3 FOR JSON PATH ) AS [schedules.schedule_things], ( SELECT ScheduleOtherThingId, OtherThing FROM #ScheduleOtherThings WHERE ScheduleId = s.ScheduleId FOR JSON PATH ) AS [schedules.schedule_other_things] from #Schedules s where s.ScheduleId = 1 for json path, root('schedules') ) fr(result) ;
result
{"schedules":[{"schedules":{"schedule_id":1,"schedule_name":"A","schedule_things":[{"ScheduleThingId":1,"Thing":10.22},{"ScheduleThingId":2,"Thing":11.02}],"schedule_other_things":[{"ScheduleOtherThingId":1,"OtherThing":"Always"},{"ScheduleOtherThingId":2,"OtherThing":"Sometimes"}]}}]}
 hidden batch(es)


select fr.result from ( select s.ScheduleId as [schedule_id], s.ScheduleName as [schedule_name], schedule_things.ScheduleThingId as [schedule_thing_id], schedule_things.Thing as [thing], schedule_other_things.ScheduleOtherThingId as [schedule_other_thing_id], schedule_other_things.OtherThing as [other_thing] from #Schedules s join #ScheduleThings schedule_things on schedule_things.ScheduleId = s.ScheduleId join #ScheduleOtherThings schedule_other_things on schedule_other_things.ScheduleId = s.ScheduleId where s.ScheduleId = 1 and schedule_things.ScheduleThingId < 3 for json auto, root('schedules') ) fr(result) ;
result
{"schedules":[{"schedule_id":1,"schedule_name":"A","schedule_things":[{"schedule_thing_id":1,"thing":10.22,"schedule_other_things":[{"schedule_other_thing_id":1,"other_thing":"Always"},{"schedule_other_thing_id":2,"other_thing":"Sometimes"}]},{"schedule_thing_id":2,"thing":11.02,"schedule_other_things":[{"schedule_other_thing_id":1,"other_thing":"Always"},{"schedule_other_thing_id":2,"other_thing":"Sometimes"}]}]}]}
 hidden batch(es)