sql - Querying all data from multiple tables -

so have 3 tables...structured so

a location table

locationid       locationname  1                location 1 2                location 2 3                location 3 

a sub area table

subareaid       locationid      subareaname 1               1               subname1 2               1               subname2 3               2               subname3 4               2               subname4 

and details table

subareaid       locationid      username 1               1               bob 2               1               frank 3               2               jeff null            3               dave 

• every location can have sub area
• every sub area can have details
• every sub area must have location
• every details can have sub area

• every details must have location

  ideally, result follows. how structure query?

  location name      sub area name     username location 1         subname1          bob location 1         subname2          frank location 2         subname3          jeff location 2         subname4          null location 3         null              dave 

the thing seems throwing me off potential nulls

select a.locationid, a.locationname, b.subareaid, b.subareaname, c.username location join subarea b on a.locationid = b.locationid full join detail c on a.locationid = c.locationid 

however, not produce desired results. input/advice?

try this

select a.locid, a.locname, b.subareaid, b.subareaname, c.username location     left join subarea b on a.locid = b.locid     outer apply (select c.username               details c               a.locid = c.locid                   , (c.subareaid = b.subareaid or b.subareaid null)     ) c 

edit: try query outer apply.

also check out [sql fiddle] (sqlfiddle.com/#!3/b4bce/5ce/5) demo.