How can I do a left outer join where both tables have a where clause?
- by cdeszaq
Here's the scenario:
I have 2 tables:
CREATE TABLE dbo.API_User
(
id int NOT NULL,
name nvarchar(255) NOT NULL,
authorization_key varchar(255) NOT NULL,
is_active bit NOT NULL
) ON [PRIMARY]
CREATE TABLE dbo.Single_Sign_On_User
(
id int NOT NULL IDENTITY (1, 1),
API_User_id int NOT NULL,
external_id varchar(255) NOT NULL,
user_id int NULL
) ON [PRIMARY]
What I am trying to return is the following:
is_active for a given authorization_key
The Single_Sign_On_User.id that matches the external_id/API_User_id pair if it exists or NULL if there is no such pair
When I try this query:
SELECT Single_Sign_On_User.id, API_User.is_active
FROM API_User LEFT OUTER JOIN
Single_Sign_On_User ON Single_Sign_On_User.API_User_id = API_User.id
WHERE
Single_Sign_On_User.external_id = 'test_ext_id' AND
API_User.authorization_key = 'test'
where the "test" API_User record exists but the "test_ext_id" record does not, and with no other values in either table, I get no records returned.
When I use:
SELECT Single_Sign_On_User.id, API_User.is_active
FROM API_User LEFT OUTER JOIN
Single_Sign_On_User ON Single_Sign_On_User.API_User_id = API_User.id
WHERE
API_User.authorization_key = 'test'
I get the results I expect (NULL, 1), but that query doesn't allow me to find the "test_ext_id" record if it exists but would give me all records associated with the "test" API_User record.
How can I get the results I am after?
