Can a Database View be used to create a Self Join?

[James Fricker]

Is it possible to create a Database View that implements a table self join?

For example I would like to find all sys_user records where there are duplicate email addresses

(the incoming email to user matching process obviously has a problem with duplicate email addresses)

something like....

select a.user_name, a.active, b.user_name, b.active, a.email
from sys_user a, sys_user b
where
a.email is not NULL and
b.email = a.email and
b.sys_id > a.sys_id

Can this be done using a Database View?

I have tried creating a view with two view tables, both set to the sys_user table but with different variable prefixes...

view table 1
table: sys_user
prefix: a
where clause: a_email IS NOT NULL

view table 2
table: sys_user
prefix: b
where clause: (b_email = a_email) && (b.sys_id > a.sys_id)

but when I click on the Try It link nothing seems to happen and I just get thrown out to the list of all db views.

Anyone have any suggestions?

[Frank_Wilhelm]

What you are trying to do should work. I did something similar to report on the languages in the knowledge base.

I think the problem is in your where clauses.

First I do not think the "a_email IS NOT NULL" will work. Instead you should try to just filter out empty strings "a_email != ''" but actually I do not think this is necessary and it will not be joined on empty values.
Just remove the where clause and look at the result.

The second problem is in your second where clause you have a typo. The following should work:
b_email = a_email && b_sys_id > a_sys_id

[James Fricker]

I removed "a_email IS NOT NULL" and fixed the typo and it now works. Many thanks Frank.

[Mark Stanger]

I've tried joining the same table to itself in a DB view and at the time it wouldn't work. If Frank's suggestion doesn't work, there are some other options you might try. Here's a good article explaining some alternatives.

http://community.servicenow.com/blog/mkaufman/duplicate-record-scripts