问题描述:

I'v got this Problem on SQLPlus:

From this code:

select distinct username, name, surname

from users

where username in ('user1', 'user2');

I get:

username name surname

--------------- -------------- -------------

user1 Alex Ander

but I need:

username name surname

--------------- -------------- -------------

user1 Alex Ander

user2 not exists not exists

Or something like this. If an User do not Exists, the Table have to write the Username, and the rest something like "not exists" too,

Please Help,

Thanks,

网友答案:

This should also work;

select distinct T.username, 
                coalesce(u.name, 'not exists'), 
                coalesce(u.surname, 'not exists')
from  (values ('user1'),('user2'),('user3')) as T(username)
      left join Users u on T.username = u.username
网友答案:
select  distinct username
,       coalesce(name, 'not exists')
,       coalesce(surname, 'not exists')
from    (
        select 'user1' as username
        union all
        select 'user2'
        ) list
left join
        users
on      list.username = users.username
网友答案:
SELECT DISTINCT username,
ISNULL(Name,'Not Exists'),
ISNULL(Surname,'Not Exists')
FROM users
  WHERE (username IN ('user1','user2') OR username IS NULL)
网友答案:

Hope to explain it now more clearly:

The original code what I have now is:

select distinct username, name, surname
from users u, accounts a
where u.user_nr = a.user_nr
and username in (
'existing_user',
'not_existing_user'
) order by username;

and it gives me:

USERNAME                  NAME            SURNAME  
------------------------- --------------- ---------------
existing_user              Hello           All

1 row selected.

and I need:

USERNAME                  NAME            SURNAME  
------------------------- --------------- ---------------
existing_user             Hello           All
not_existing_user     Not Exists      Not Exists

2 row selected.

The Problem: the user not_existing_user is not existing in the DataBase, but the query has to show him anyway from the code with the Info - User not in the DB. For 500 Users I can not check everyone separate :/

相关阅读:
Top