How can I do a right outer join where both tables have a where clause?

Posted by cdeszaq on Stack Overflow See other posts from Stack Overflow or by cdeszaq
Published on 2010-03-31T21:11:13Z Indexed on 2010/03/31 21:13 UTC
Read the original article Hit count: 309

Filed under:

sql

|

sql-server

|

outer-join

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?

Developer IT