outer join vs. =*

I'm using SQL Server 2000 (and C#). I have a table of locations, a table of people and a table of locations in which the people are willing to work, like this (with sample data):

create table Person (PersonID int, PerName varchar(20))
create table Location (LocationID int, LocName varchar(10))
create table PersonLocation (PersonID int, LocationID int)
insert into Person value (1, 'Joe')
insert into Person value (2, 'Mary')
insert into Person value (3, 'Pat')
insert into Location value (10, 'Home')
insert into Location value (20, 'Office')
insert into Location value (30, 'The moon')
insert into PersonLocation (1, 10)
insert into PersonLocation (1, 30)
insert into PersonLocation (2, 20)

I want to create a CheckBoxList with all the items from the Location table and check off the items which exist in the PersonLocation table. I thought it would be good to do this in one query of the database. Here's the results I'd to populate the list:

select LocationID, Selected from SelectedLocations where PersonID = 1
10 10
20 null
30 30
select LocationID, Selected from SelectedLocations where PersonID = 2
10 null
20 2
30 null
select LocationID, Selected from SelectedLocations where PersonID = 3
10 null
20 null
30 null

That second column could also be true/false (bit 0/1) or whatever. I'm going to subclass the CheckBoxList control and add a DataSelectedField property which would be the name of the field which indicates if the item is selected. I will need to override the databinding, so any value that distinguishes selected locations from unselected locations if fine.

I like to use views for queries so that I can add in arbitrary filtering and sorting. So I made this view:

create view SelectedLocations
as
select l.LocationID, pl.LocationID Selected
from PersonLocation pl, Location l
where l.LocationID *= pl.LocationID

But it isn't standard SQL. I'm confused about why it works. Is there a standardized way to do the query I want in SQL? I think it should have an outer join, but I tried various outer join syntax with no luck.You need something like this:

SELECT
 L.LocationID,
 PL.LocationID AS Selected
FROM
 Location L
LEFT OUTER JOIN
 PersonLocation PL ON PL.LocationID = L.LocationID AND PL.PersonID = '1'

Terri|||Thank you.

Is there any way to turn that query into a view where PersonID is a column in the view? I suppose this is an example of a place where I would need to use a stored procedure instead of a view. This is too bad, because I'm then prevented from using arbitrary sorting.|||You should be able to create a view using this approach:

CREATE VIEW dbo.View1
AS
SELECT
 L.LocationID,
 PL.LocationID AS Selected,
 P.PersonID
FROM
 Location L
FULL JOIN
 Person P ON 1=1
LEFT OUTER JOIN
 PersonLocation PL ON PL.LocationID = L.LocationID AND PL.PersonID = P.PersonID

So, you'd use it like this:

SELECT * FROM View1 WHERE PersonID = 1
SELECT * FROM View1 WHERE PersonID = 2
SELECT * FROM View1 WHERE PersonID = 3

etc.

Terri|||Thank you very much, indeed!