sql server misc: Hi all,

I have three tables:

Entity
ID (PK)
Name (varchar(255))

Key
ID (PK)
Name (varchar(255))

Value
ID (PK)
KeyID (FK)
EntityID (FK)
Value (datetime)

There is a many-to-many relationship between entities and keys via the
values table. I need a query that will return all possible keys and,
if a value exists for a for an entity in the value table, return that
value, else return null for the particular key. For example

There are two keys (Date 1 and Date 2). Entity 1 has a record in Value
for Date 1, but not Date 2. Here's the recordset I'm trying to get:

Entity.ID Key.ID Key.Name Value.Value
1 1 Form Date 6/5/1978
1 2 Term Date Null

The purpose of the query is to serch for unpopulated values for all
possible keys one entity at a time. Any help is appreciated.

Thanks,
Johnny

Update:

I think I need a nested SQL statement, but I'm having trouble with the
syntax.

I need to left join SELECT Key.ID, Key.Name FROM Key and SELECT
Value.Value FROM Value WHERE EntityID = 1 on Key.KeyID = Value.KeyID.
The second SQL statement needs to execute first, and then I need to
join to it with the first. Can anyone help me with the syntax here?

Thanks,
Johnny

I posted too soon! For posterity, here's the answer:

SELECT K.Name as KeyName,S.Value as KeyValue FROM [Key] AS K LEFT JOIN
(SELECT V.* FROM [Value] AS V WHERE V.EntityID=1) AS S ON K.ID =
S.KeyID;