Is Database view gives duplicate records

[JineshM1]

HI,

I am trying to create a DB view where names which are available in user table with u_employee_type (choice field) as value 'Contractor' but the name is not in other table (u_contractor) but it gives me duplicate records. Can anyone suggest what should be the correct where clause . Below snap shot the field u_employee_type is a choice field . I am new in this so please appreciate if anyone can suggest

Thanks.

https://community.servicenow.com/87c23b3bdbf07410190dfb243996195b.iix

[palanikumar]

Hi,

You may need to do following changes:

1) Remove sys_choice table. I'm not sure why sys_choice table is added there. Hope it is not required to join. You can directly refer the choice field in your condition.

2) Set left join field to true for u_contractors table. This will display null value if no matching contractor record

3) Where clause should always have matching condition between two table. If you give non matching condition then it will always return duplicate record. So your matching condition should be:

usr_sys_id=con_u_name

Assuming u_name field in contractors table is reference to user record. Update the field if my assumption is not correct

4) Now your will return all User record. In contractor field it will show value if it has matching record otherwise it will show null.

5) To get the list of users who are not in contractors table, open the data base view in list view (Click Try it in database view) and add filter to match record where contractors are emty

Thank you,

Palani