column alias oracle 10g

Question

This seems so simple but I can't understand how do I do this query: I have a table users like this:

user_id | name | role
1       | abc  | a
2       | lol  | b
3       | f    | c

and a table usersprojects (with user and project PKs)

projectid | userid
1         | 1
1         | 2
2         | 2
2         | 3

How could I select all users columns and also a boolean column alias "assigned" to project "1"

I would like a result like this:

user_id | name | role | assigned
1       | abc  | a    | true
2       | lol  | b    | true
3       | f    | c    | false

The query wouldn't be something like this:

 Select user_id ,name, role,
  (users.user_id in (Select user_id from usersprojects where projectid=1)
    ) assigned;

But it doesn't work... what is the right way to do this query?

Answer 1

 SELECT u.user_id ,name, role, NVL(projectId, 0) assigned
 FROM users u LEFT JOIN userprojects up ON (u.user_id = up.userid)

Answer 2

You need a left outer join. Keep in mind that there is no boolean data type in Oracle.

Answer 3

SELECT
    user_id, name, role,
    CASE WHEN (SELECT COUNT(*) FROM UsersProjects up #
               WHERE up.user_id = u.user_id) > 0 
         THEN 'true' ELSE 'false' END assigned
FROM Users u

Answer 4

select u.user_id as user_id, 
       u.name as name, 
       u.role as role, 
       decode(up.user_id, NULL, 'false', 'true') AS assigned
from users u, usersprojects up
where  up.projectid=1
and u.user_id = up.user_id(+)