Solved

Help with Search Query in SQL Server 2008R2 Version

Posted on 2016-10-30
26
57 Views
Last Modified: 2016-11-14
I have a database table that holds records. The table column are company id, department id, section id ,document title and another table that hold the access roles to these records based employee assigned roles.

For eg. DOCUMENT_MANAGEMENT TABLE>>>

ID, COMPANY_ID,DEPARTMENT_ID,SECTION_ID,DOCUMENT_TITLE,DOCUMENT_DESCRIPTION
1,1,1,1,'Procedure for HR Hiring','some data'
2,1,1,2,'Resumes Submissions','some data'
3,1,2,1,'Study of Engineering Designs','some data'
4,1,2,2,'Engineering Architecture','some data'

etc.

The table rows need to be displayed based on a specific role.

And hence the query results needs to be tied to Employee Roles.

EMPLOYEE_ROLE_TABLE>>
ID,EMPLOYEE_ID,DEPARTMENT_ID,SECTION_ID
1,1,1,1
2,1,2,-1
3,2,1,-1
4,2,2,-1

If you noticed the above table, the employee with EMPLOYEE_ID (1), should have access to the Department ID (1 & 2) but the Section ID is limited to section 1 for department 1 and all section ID (denoted by -1 value) for department 2.

In short, the Employee 1 can access all the Sections of department 2 but is restricted to only section 1 of department 1.

Likewise, the Employee 2 can access all the Sections of department 1 & 2.

My idea was to restrict the access by the following query and ensuring that the user role table is linked to the primary query.

SELECT DOCUMENT_TITLE FROM DOCUMENT_MANAGEMENT
WHERE
(DEPARTMENT_ID=-1  OR -1=-1 ) AND
(SECTION_ID=-1   OR -1=-1   ) AND
(UPPER(DOCUMENT_TITLE ) Like '%' +@SEARCH_STR + '%' OR UPPER(DOCUMENT_DESCRIPTION ) Like '%' +@SEARCH_STR + '%' ) AND
(DEPARTMENT_ID IN (SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEE_ROLE_TABLE WHERE USER_ID=1) OR DEPARTMENT_ID=-1) AND
          (SECTION_ID IN (SELECT DISTINCT SUB_DEPARTMENT_ID FROM EMPLOYEE_ROLE_TABLE WHERE USER_ID=1) OR SECTION_ID=-1)  

The issue I am having is for The record #2, is not returned by this query for employee id 1 who has the permission to access all the sections. As the value of in the employee role table is -1 (which indicates that whatever value is stored in the section_Id column of table DOCUMENT_MANAGEMENT should be visible to this employee.

What is wrong with the query I had written.
0
Comment
Question by:Member_2_7967119
  • 11
  • 9
  • 6
26 Comments
 

Author Comment

by:Member_2_7967119
ID: 41865815
The query should read as

SELECT DOCUMENT_TITLE FROM DOCUMENT_MANAGEMENT
WHERE
(DEPARTMENT_ID=-1  OR -1=-1 ) AND
(SECTION_ID=-1   OR -1=-1   ) AND
(UPPER(DOCUMENT_TITLE ) Like '%' +@SEARCH_STR + '%' OR UPPER(DOCUMENT_DESCRIPTION ) Like '%' +@SEARCH_STR + '%' ) AND
(DEPARTMENT_ID IN (SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEE_ROLE_TABLE WHERE USER_ID=1) OR DEPARTMENT_ID=-1) AND
          (SECTION_ID IN (SELECT DISTINCT SECTION_ID FROM EMPLOYEE_ROLE_TABLE WHERE USER_ID=1) OR SECTION_ID=-1)
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41865845
Hi,

could you provide some data and the expected output.

Regards,
Pawan
0
 

Author Comment

by:Member_2_7967119
ID: 41865855
No, If I provide as stated OR DEPARTMENT_ID=-1
Won't it retrieve all records that match DEPARTMENT_ID=-1 and not use the Employee Role Critiria?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41865861
So if DEPARTMENT_ID=-1, then you dont want Employee Role Criteria filter. ?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41865869
i think issue is some where else. select all the columns and then campare the row data with the query.
0
 

Author Comment

by:Member_2_7967119
ID: 41865880
Pawan,

Attached the tables scripts and also the stored procedure. I also provided the insert scripts.

If the search string passed to the stored procedure is 'Engineering' then the record 2, 4 should show up in the resultant output.

I also provide the execution parameters I use.
SCRIPTS_DMS.txt
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41866329
Hi,

Comment this line - --AND (SECTION_ID IN (SELECT DISTINCT SECTION_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=1) OR SECTION_ID = -1 )

Run this code and see the output

--


DECLARE @SEARCH_STR AS VARCHAR(1000) = 'Engineering'
SELECT DEPARTMENT_ID,SECTION_ID,DOCUMENT_TITLE  FROm DOCUMENT_MANAGEMENT
WHERE 1=1
AND (UPPER(DOCUMENT_TITLE ) Like '%' +@SEARCH_STR + '%' OR UPPER(DOCUMENT_DESCRIPTION ) Like '%' +@SEARCH_STR + '%' )
AND (DEPARTMENT_ID=-1  OR -1=-1 )
AND (SECTION_ID=-1   OR -1=-1   ) 
AND (DEPARTMENT_ID IN (SELECT DISTINCT DEPARTMENT_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=1) OR DEPARTMENT_ID=-1)
--AND (SECTION_ID IN (SELECT DISTINCT SECTION_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=1) OR SECTION_ID = -1 )

--

Open in new window


O/p
------------

Run the below code now --

DEPARTMENT_ID      SECTION_ID      DOCUMENT_TITLE
2                               2      Engineering Resumes Submissions
1                             10      Study of Engineering Designs
2                             1      Engineering Architecture

--
--

SELECT DISTINCT SECTION_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=1

--

Open in new window


O/p
-----------------------
-1
1

Now from the second query we are getting values Section_id = 1 or -1 and from the first query we are getting 2,10 and 1. So when we match we get only 1 record with Section_id = 1 as this is only value that matches using the in clause, also note that we dont have any value with SECTION_ID = -1.

The record ID = 2 is not coming because it has Section_ID = 2.

Hope it helps.!!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41866457
-1 indicates no section restriction

Do you also have -1 entries for department?
0
 

Author Comment

by:Member_2_7967119
ID: 41866501
Pawan,

I do understand that if i remove the query that refer to the section_id it will work. My intend is that if section_id does not match, then it should negate this line of statement and fetch all the sections under department id.

In short, if the value of employee_role_id is -1 then all the section should be visible to the employee but if the employee_role_id is any other value, only those section should be visible to the employee.

Sorry if I haven't stated that before.

If Department_id is =-1 it would mean that the employee is a super user and has access to all the content.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41866628
I added a super user (assume both department_id and section_id = -1)

Using a condtional inner join - I believe - will meet you requirement. Please check the results seen below (each is for a different user)
select
*
from document_management d
inner join employee_role e 
on (d.department_id = e.department_id and d.section_id = e.section_id and e.department_id > 0 and e.section_id > 0)
or (d.department_id = e.department_id and e.department_id > 0 and e.section_id = -1)
or (e.department_id = -1 and e.section_id = -1)
where e.employee_id = 0


| ID | COMPANY_ID | DEPARTMENT_ID | SECTION_ID |               DOCUMENT_TITLE | DOCUMENT_DESCRIPTION | ID | EMPLOYEE_ID | DEPARTMENT_ID | SECTION_ID |
|----|------------|---------------|------------|------------------------------|----------------------|----|-------------|---------------|------------|
|  1 |          1 |             1 |          1 |      Procedure for HR Hiring |            some data |  0 |           0 |            -1 |         -1 |
|  2 |          1 |             1 |          2 |          Resumes Submissions |            some data |  0 |           0 |            -1 |         -1 |
|  3 |          1 |             2 |          1 | Study of Engineering Designs |            some data |  0 |           0 |            -1 |         -1 |
|  4 |          1 |             2 |          2 |     Engineering Architecture |            some data |  0 |           0 |            -1 |         -1 |


select
*
from document_management d
inner join employee_role e 
on (d.department_id = e.department_id and d.section_id = e.section_id and e.department_id > 0 and e.section_id > 0)
or (d.department_id = e.department_id and e.department_id > 0 and e.section_id = -1)
or (e.department_id = -1 and e.section_id = -1)
where e.employee_id = 1



| ID | COMPANY_ID | DEPARTMENT_ID | SECTION_ID |               DOCUMENT_TITLE | DOCUMENT_DESCRIPTION | ID | EMPLOYEE_ID | DEPARTMENT_ID | SECTION_ID |
|----|------------|---------------|------------|------------------------------|----------------------|----|-------------|---------------|------------|
|  1 |          1 |             1 |          1 |      Procedure for HR Hiring |            some data |  1 |           1 |             1 |          1 |
|  3 |          1 |             2 |          1 | Study of Engineering Designs |            some data |  2 |           1 |             2 |         -1 |
|  4 |          1 |             2 |          2 |     Engineering Architecture |            some data |  2 |           1 |             2 |         -1 |


select
*
from document_management d
inner join employee_role e 
on (d.department_id = e.department_id and d.section_id = e.section_id and e.department_id > 0 and e.section_id > 0)
or (d.department_id = e.department_id and e.department_id > 0 and e.section_id = -1)
or (e.department_id = -1 and e.section_id = -1)
where e.employee_id = 2


| ID | COMPANY_ID | DEPARTMENT_ID | SECTION_ID |               DOCUMENT_TITLE | DOCUMENT_DESCRIPTION | ID | EMPLOYEE_ID | DEPARTMENT_ID | SECTION_ID |
|----|------------|---------------|------------|------------------------------|----------------------|----|-------------|---------------|------------|
|  1 |          1 |             1 |          1 |      Procedure for HR Hiring |            some data |  3 |           2 |             1 |         -1 |
|  2 |          1 |             1 |          2 |          Resumes Submissions |            some data |  3 |           2 |             1 |         -1 |
|  3 |          1 |             2 |          1 | Study of Engineering Designs |            some data |  4 |           2 |             2 |         -1 |
|  4 |          1 |             2 |          2 |     Engineering Architecture |            some data |  4 |           2 |             2 |         -1 |

Open in new window

see: http://sqlfiddle.com/#!6/92875/5
    CREATE TABLE DOCUMENT_MANAGEMENT
        ([ID] int, [COMPANY_ID] int, [DEPARTMENT_ID] int, [SECTION_ID] int, [DOCUMENT_TITLE] varchar(32), [DOCUMENT_DESCRIPTION] varchar(13))
    ;
        
    INSERT INTO DOCUMENT_MANAGEMENT
        ([ID], [COMPANY_ID], [DEPARTMENT_ID], [SECTION_ID], [DOCUMENT_TITLE], [DOCUMENT_DESCRIPTION])
    VALUES
        (1, 1, 1, 1, 'Procedure for HR Hiring', 'some data'),
        (2, 1, 1, 2, 'Resumes Submissions', 'some data'),
        (3, 1, 2, 1, 'Study of Engineering Designs', 'some data'),
        (4, 1, 2, 2, 'Engineering Architecture', 'some data')
    ;
    
    CREATE TABLE EMPLOYEE_ROLE
        ([ID] int, [EMPLOYEE_ID] int, [DEPARTMENT_ID] int, [SECTION_ID] int)
    ;
        
    INSERT INTO EMPLOYEE_ROLE
        ([ID], [EMPLOYEE_ID], [DEPARTMENT_ID], [SECTION_ID])
    VALUES
        (0, 0, -1, -1),
        (1, 1, 1, 1),
        (2, 1, 2, -1),
        (3, 2, 1, -1),
        (4, 2, 2, -1)
    ;

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41866653
sorry there were some redundancies in the above joins, the following version is simpler

By the way I assume you will just add any wanted text search on top of this security logic.
select
*
from document_management d
inner join employee_role e 
on (d.department_id = e.department_id and d.section_id = e.section_id)
or (d.department_id = e.department_id and e.section_id = -1)
or (e.department_id = -1 and e.section_id = -1)
where e.employee_id = 0
;

select
*
from document_management d
inner join employee_role e 
on (d.department_id = e.department_id and d.section_id = e.section_id)
or (d.department_id = e.department_id and e.section_id = -1)
or (e.department_id = -1 and e.section_id = -1)
where e.employee_id = 1
;

select
*
from document_management d
inner join employee_role e 
on (d.department_id = e.department_id and d.section_id = e.section_id)
or (d.department_id = e.department_id and e.section_id = -1)
or (e.department_id = -1 and e.section_id = -1)
where e.employee_id = 2
;

Open in new window

Results:
| ID | COMPANY_ID | DEPARTMENT_ID | SECTION_ID |               DOCUMENT_TITLE | DOCUMENT_DESCRIPTION | ID | EMPLOYEE_ID | DEPARTMENT_ID | SECTION_ID |
|----|------------|---------------|------------|------------------------------|----------------------|----|-------------|---------------|------------|
|  1 |          1 |             1 |          1 |      Procedure for HR Hiring |            some data |  0 |           0 |            -1 |         -1 |
|  2 |          1 |             1 |          2 |          Resumes Submissions |            some data |  0 |           0 |            -1 |         -1 |
|  3 |          1 |             2 |          1 | Study of Engineering Designs |            some data |  0 |           0 |            -1 |         -1 |
|  4 |          1 |             2 |          2 |     Engineering Architecture |            some data |  0 |           0 |            -1 |         -1 |
        

| ID | COMPANY_ID | DEPARTMENT_ID | SECTION_ID |               DOCUMENT_TITLE | DOCUMENT_DESCRIPTION | ID | EMPLOYEE_ID | DEPARTMENT_ID | SECTION_ID |
|----|------------|---------------|------------|------------------------------|----------------------|----|-------------|---------------|------------|
|  1 |          1 |             1 |          1 |      Procedure for HR Hiring |            some data |  1 |           1 |             1 |          1 |
|  3 |          1 |             2 |          1 | Study of Engineering Designs |            some data |  2 |           1 |             2 |         -1 |
|  4 |          1 |             2 |          2 |     Engineering Architecture |            some data |  2 |           1 |             2 |         -1 |
        

| ID | COMPANY_ID | DEPARTMENT_ID | SECTION_ID |               DOCUMENT_TITLE | DOCUMENT_DESCRIPTION | ID | EMPLOYEE_ID | DEPARTMENT_ID | SECTION_ID |
|----|------------|---------------|------------|------------------------------|----------------------|----|-------------|---------------|------------|
|  1 |          1 |             1 |          1 |      Procedure for HR Hiring |            some data |  3 |           2 |             1 |         -1 |
|  2 |          1 |             1 |          2 |          Resumes Submissions |            some data |  3 |           2 |             1 |         -1 |
|  3 |          1 |             2 |          1 | Study of Engineering Designs |            some data |  4 |           2 |             2 |         -1 |
|  4 |          1 |             2 |          2 |     Engineering Architecture |            some data |  4 |           2 |             2 |         -1 |

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41867670
>>"The record #2, is not returned by this query for employee id 1 who has the permission to access all the sections. "

    INSERT INTO EMPLOYEE_ROLE
        ([ID], [EMPLOYEE_ID], [DEPARTMENT_ID], [SECTION_ID])
    VALUES
   
        (1, 1, 1, 1),
        (2, 1, 2, -1),

I understand that data to indicate the following:
user 1 has access to section 1 or department 1
user 1 has access to all of department 2

The record #2 is in department 1 section 2, and therefore user 1 should be denied that document
... I think
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41867889
Try this Updated..

@deptId

DECLARE @deptId AS INT = 1
DECLARE @SEARCH_STR AS VARCHAR(1000) = 'Engineering'

SELECT DEPARTMENT_ID,SECTION_ID,DOCUMENT_TITLE  FROm DOCUMENT_MANAGEMENT
WHERE 1=1
AND (UPPER(DOCUMENT_TITLE ) Like '%' +@SEARCH_STR + '%' OR UPPER(DOCUMENT_DESCRIPTION ) Like '%' +@SEARCH_STR + '%' )
AND (DEPARTMENT_ID=-1  OR -1=-1 )
AND (SECTION_ID=-1   OR -1=-1   ) 
AND (DEPARTMENT_ID IN (SELECT DISTINCT DEPARTMENT_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=1) OR DEPARTMENT_ID=-1)
AND (DEPARTMENT_ID=@deptId OR (SECTION_ID IN (SELECT DISTINCT SECTION_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=1) OR SECTION_ID = -1 ))

--

Open in new window


Hope it helps  !!
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Member_2_7967119
ID: 41868394
Are the following statement  on line 7 & 8, relevant?

AND (DEPARTMENT_ID=-1  OR -1=-1 )
AND (SECTION_ID=-1   OR -1=-1   )

Also the following statement,
AND (DEPARTMENT_ID IN (SELECT DISTINCT DEPARTMENT_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=1) OR DEPARTMENT_ID=-1)

If the DEPARMENT_ID=-1 is true, then the check of employee role would be bypassed.

Please illustrate. Thank you.
0
 
LVL 17

Accepted Solution

by:
Pawan Kumar Khowal earned 250 total points
ID: 41868465
It worked ?

No first 2 statement we can remove...

Below looks ok to me.

AND (DEPARTMENT_ID IN (SELECT DISTINCT DEPARTMENT_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=1) OR DEPARTMENT_ID=-1)
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 41869096
Why are you not considering the use of a conditional join? It is efficient and helps srperate the security logic from the filtering logic?
select
*
from document_management d
  /* security logic */
inner join employee_role e 
on (d.department_id = e.department_id and d.section_id = e.section_id)
or (d.department_id = e.department_id and e.section_id = -1)
or (e.department_id = -1 and e.section_id = -1)
  /* filtering logic */
WHERE e.employee_id = 2
AND ( UPPER(DOCUMENT_TITLE ) Like '%' +@SEARCH_STR + '%' 
    OR UPPER(DOCUMENT_DESCRIPTION ) Like '%' +@SEARCH_STR + '%' 
         )  

Open in new window


Also. I asked why  record 2 should be allowed to user 1.  Could you answer please.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41871707
Hi Member_2_7967119,
A feedback will be appreciated.

Regards,
Pawan
0
 

Author Comment

by:Member_2_7967119
ID: 41878259
Still working on the same. I will update in a day.
0
 

Author Comment

by:Member_2_7967119
ID: 41882795
Responding to  "I asked why  record 2 should be allowed to user 1."

As per the data In the EMPLOYEE_ROLE_REGISTRATION the user 1 should have access to department id 1 and section id 1 and also should have access to all sections under department 2. (as -1 in the section id represents that the user has full access).

To illustrate it further, if I add additional column sub_section_id and if it has a value of  -1, and section id has a value greater than 0, then the user 1 can access content of department id, section id 1 and all sub section ids.

Actually I am having 5 levels in my representation.
0
 

Author Comment

by:Member_2_7967119
ID: 41882803
Pawan,

The following statement won't work as if the department_id passed is -1, then the query would return all records where department_id=-1 and would not use the reference to the Employee ROle Registration table? Am i missing something here ?


AND (DEPARTMENT_ID IN (SELECT DISTINCT DEPARTMENT_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=1) OR DEPARTMENT_ID=-1)
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41883234
What you want as output in above case?
0
 

Author Comment

by:Member_2_7967119
ID: 41884102
Pavan,

You were right,  with the query

AND (DEPARTMENT_ID IN (SELECT DISTINCT DEPARTMENT_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=1) OR DEPARTMENT_ID=-1) , I was using the following

AND (DEPARTMENT_ID IN (SELECT DISTINCT DEPARTMENT_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=1) OR @DEPARTMENT_ID=-1), which was returning records if I do not pass any department and overriding the check on the security table.


Now I added a 2 more columns to the table, namely LEVEL1_ID and LEVEL2_ID.

and changed the scripts to the following. Please advise if I am doing it rightly.

DECLARE @deptId AS INT = 11
DECLARE @sectId AS INT = 1052
DECLARE @level1Id AS INT = 1051
DECLARE @level2Id AS INT = 4118
DECLARE @USER_ID AS INT=1
DECLARE @SEARCH_STR AS VARCHAR(1000) = ''

SELECT DOCUMENT_NAME,DEPARTMENT_ID,SECTION_ID,LEVEL1_ID,LEVEL2_ID  FROM DOCUMENT_MANAGEMENT
WHERE  1=1
AND (UPPER(DOCUMENT_NAME ) Like '%' +@SEARCH_STR + '%' OR UPPER(DOCUMENT_DESCRIPTION ) Like '%' +@SEARCH_STR + '%' )
AND (DEPARTMENT_ID IN (SELECT DISTINCT DEPARTMENT_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=@USER_ID) OR DEPARTMENT_ID=-1)
AND (DEPARTMENT_ID=@deptId OR (SECTION_ID IN (SELECT DISTINCT SECTION_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=1) OR SECTION_ID = -1 ))
AND (SECTION_ID IN (SELECT DISTINCT SECTION_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=@USER_ID) OR SECTION_ID=-1)
AND (DEPARTMENT_ID=@deptId OR SECTION_ID=@sectId  OR (SECTION_ID IN (SELECT DISTINCT SECTION_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=1) OR SECTION_ID = -1 ))
AND (LEVEL1_ID IN (SELECT DISTINCT LEVEL1_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=@USER_ID) OR LEVEL1_ID=-1)
AND (DEPARTMENT_ID=@deptId OR SECTION_ID=@sectId OR LEVEL1_ID=@level1Id  OR (LEVEL1_ID IN (SELECT DISTINCT LEVEL1_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=1) OR LEVEL1_ID = -1 ))
AND (LEVEL2_ID IN (SELECT DISTINCT LEVEL2_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=@USER_ID) OR LEVEL2_ID=-1)
AND (DEPARTMENT_ID=@deptId OR SECTION_ID=@sectId OR LEVEL1_ID=@level1Id OR LEVEL2_ID=@level2Id OR (LEVEL2_ID IN (SELECT DISTINCT LEVEL2_ID FROM [EMPLOYEE_ROLE_REGISTRATION] WHERE USER_ID=1) OR LEVEL2_ID = -1 ))


I have posted the output based on each input parameter being passed with the expected results. There is some issue with the script.

I am attaching the table scripts , table data and the output expected based on each paramater change.


Thank you in advance for all the help.
ee.txt
eeeOutput.txt
0
 

Author Comment

by:Member_2_7967119
ID: 41885792
Should I ask this as a separate question?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41885794
Yes please, Thank you !, .. :)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41885821
WHY ask a new question? I would not recommend it in this case. THIS question isn't resolved.
0
 

Author Closing Comment

by:Member_2_7967119
ID: 41885927
Thank you for your help .
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now