CODEWITHSUNDEEP
sqlsql-server-2005
Khrys
Khrys

Reputation: 2774

Return rows of found and not found

Is it possible to return all rows based in condition? Example:

SELECT
 *
FROM
 TABLE
WHERE
 USER IN ('A', 'B', 'C')

In the example above it will return only if there is a user A, B and C. If The user C doesn't exist it will return only 2 rows, A and B. I would like to have 3 rows, and the C with empty.

Thanks.

Upvotes: 1

Views: 42

Answers (2)

Jayanti Lal
Jayanti Lal

Reputation: 1185

You can take advantage of LEFT join here..

WITH cte AS 
(SELECT 'a' AS user UNION ALL
 SELECT 'b' 
  SELECT 'c'
 )
SELECT * FROM cte
LEFT JOIN tablename ON yourcolumnName = user

Upvotes: 2

Pham X. Bach
Pham X. Bach

Reputation: 5442

You could use this

SELECT
    t1.[user],
    t.*
FROM
(
    SELECT 'A' [user]
    UNION ALL
    SELECT 'B'
    UNION ALL
    SELECT 'C'
) t1    
LEFT JOIN 
    table_name t
ON t.[user] = t1.[user];

Upvotes: 1

Related Questions