Database with two tables, the Company: the Company, the User: the User
Company in the customer table
Id UserID name
1 5 xiamen XX trading co., LTD.
Shantou 2 6 XX textile co., LTD.
3 5 changzhou XX textile printing and dyeing co., LTD.
4 2 hangzhou XX cloth art co., LTD.
5 3 foshan XX channgzhou
6 5 foshan XX textile co., LTD.
7 2 cotton industry company in foshan XXX
User table
Id name sysName DepartmentCode
1 XX maggie Chen 00106
2 CAI XX caicai 00104
3 ko XX viline 00102
4 XX Doris liu 00103
5 XX Cindy yu 00106
6 yellow XX Amily 00104

Involves a question of authority, department number (DepartmentCode) as the '00104', is a sales department, the department
The sales staff in view, can only see their own customers,
Such as the User table '2 CAI XX caicai 00104,
The query SQL='select * from the Company left the join the User on the Company. The UserID=User. The id where 1=1 and the User. DepartmentCode=' 00104 '. And the User id=2 '
And in addition to the sales staff, can see all of the customers, do not need to specify the id
The query SQL='select * from the Company left the join the User on the Company. The UserID=User. The id where 1=1' and the User. The DepartmentCode!
='00104'
Page incoming parameters: @ UserId
My problem is that by the incoming parameters, how to combine the above conditions in a SQL execution, by judging whether incoming parameter @ UserId is belong to the sales staff to perform the query results,

I tried several ways:
. 1. (the case when the User DepartmentCode='00104'. Then the User id=@ UserId else User. Id<> NULL end)
. There is an error, then after can't User id=@ UserId
2. (the User DepartmentCode='00104' and the User. The id=@ UserId) or (User DepartmentCode!='00104' and the User id!=null)
After the query results cannot jump or always (User id!=null)
3. (the User id (in the SELECT id FROM the User WHERE DepartmentCode='00104' AND id=@ UserId)) OR (. User id is not null)
This paragraph is always judge (User id is not null), input sales staff id also check out all the results
4.

Pretty good, really can't solve this problem, please, I have a look, if it is my way out of the wrong will check it, or there's a better solution on this issue can instruct me, I thanked the here, this is all my points, beg for my disambiguation answer

CodePudding user response:

Put this logic in the application process more convenient, why to calculate in SQL processing logic,

CodePudding user response:

1. Through UserId access to user DepartmentCode
2. Through DepartmentCode different SQL execution

CodePudding user response:

reference 1/f, maple VS mark response:

put this logic in the application process more convenient, why to calculate in SQL processing logic,

Because of the limited, is the popup window of custom software interface

CodePudding user response:

refer to the second floor ayzen1988 response:

1. Through UserId access to the user's DepartmentCode
2. Through DepartmentCode perform different SQL

Truth is such a truth, but how to perform? Judgment test method, are not feasible

CodePudding user response:

reference 4 floor hkm19960524 response:

Quote: refer to the second floor ayzen1988 response:

1. Through UserId access to user DepartmentCode
2. Through DepartmentCode perform different SQL

Truth is such a truth, but how to perform? Judgment test method, there is no feasible

Don't want to use SQL, then use the stored procedure
The SELECT DepartmentCode INTO @ DepartmentCode the FROM User WHERE id=@ UserId LIMIT 1;
IF @ DepartmentCode='00104' THEN
.
The ELSE
.
END IF;

CodePudding user response:

reference 5 floor ayzen1988 reply:

Quote: refer to 4th floor hkm19960524 response:

Quote: refer to the second floor ayzen1988 response:

1. Through UserId access to user DepartmentCode
2. Through DepartmentCode perform different SQL

Truth is such a truth, but how to perform? Judgment test method, there is no feasible

Don't want to use SQL, then use the stored procedure
The SELECT DepartmentCode INTO @ DepartmentCode the FROM User WHERE id=@ UserId LIMIT 1;
IF @ DepartmentCode='00104' THEN
.
The ELSE
.
END IF;

QAQ stored procedure I would also like to, but I can only fill in the from the back of the statement, the condition is very limited

CodePudding user response:

Select * from the Company left the join the User on the Company. The UserID=User. Id
Where the case when the User. The DepartmentCode='00104'. Then the User id=@ UserId else 1=1 end

CodePudding user response:

refer to 7th floor octwind response:

select * from the Company left the join User on Company. The UserID=User. Id
Where the case when the User. The DepartmentCode='00104'. Then the User id=@ UserId else 1=1 end

brother, then, is not only the content of the return, you can't judge or assignment is? Can't use=no. After then

CodePudding user response:

SELECT * FROM the Company, the User where Company. UserID=User. The id and the User. The id=1 and the if (User DepartmentCode='00104', 1=0, 1=1);
. See you only and distinguish the condition of the User id=2, but still want to inquirers department first, so don't know how to do conditions to distinguish, I here generally spelled like the one above, conform to the additional conditions, do not accord with the 1=1