SQL View with Data from two tables
- by Alex
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.
