SQL View with Data from two tables

Question

Hello! I can't seem to crack this - I have two tables (Persons and Companies), and I'm trying to create a view that:

1) shows all persons

2) also returns companies by themselves once, regardless of how many persons are related to it

3) orders by name across both tables

To clarify, some sample data:

(Table: Companies)
Id  Name
 1  Banana
 2  ABC Inc.
 3  Microsoft
 4  Bigwig

(Table: Persons)
Id  Name      RelatedCompanyId
 1  Joe Smith 3
 2  Justin    
 3  Paul Rudd 4
 4  Anjolie
 5  Dustin    4

The output I'm looking for is something like this:

 Name      PersonName CompanyName RelatedCompanyId
 ABC Inc.  NULL       ABC Inc.    NULL
 Anjolie   Anjolie    NULL        NULL
 Banana    NULL       Banana      NULL
 Bigwig    NULL       Bigwig      NULL
 Dustin    Dustin     Bigwig      4
 Joe Smith Joe Smith  Microsoft   3
 Justin    Justin     NULL        NULL
 Microsoft NULL       Microsoft   NULL
 Paul Rudd Paul Rudd  Bigwig      4

As you can see, the new "Name" column is ordered across both tables (the company names appear correctly in between the person names), and each company appears exactly once, regardless of how many people are related to it.

Can this even be done in SQL?! P.S. I'm trying to create a view so I can use this later for easy data retrieval, fulltext indexing and make the programming side simpler by just querying the view.

Answer 1

Here's one way:

select * from (
    select Name, null as PersonName, Name as CompanyName, null as RelatedCompanyID
    from Companies
    union
    select Persons.Name as Name, Persons.Name as PersonName, Companies.Name as CompanyName, RelatedCompanyID
    from Persons
    left join Companies on Persons.RelatedCompanyID = Companies.ID
) as AggregatedData
order by AggregatedData.Name

Or slightly more readably with a common table expression, although there's no other real benefit in this case:

with AggregatedData as (
    select Name, null as PersonName, Name as CompanyName, null as RelatedCompanyID
    from Companies
    union 
    select Persons.Name as Name, Persons.Name as PersonName, Companies.Name as CompanyName, RelatedCompanyID
    from Persons
    left join Companies on Persons.RelatedCompanyID = Companies.ID
)
select * from AggregatedData
order by AggregatedData.Name