Get shopping order list based on the user roles of logged-in user

Transact-SQL
1

I want to get the list of items present in that order based on the confidentiality code of that product or Item and confidentiality code of the user.

I display the list of orders in first grid, by selecting the order in first grid I display the Items present in that order based on the confidentiality code of that item.

whenever order in 1st grid is selected i want to display the items that the item code should be less than or equal to the confidentiality code of the logged-in user other items should not display.
If the all the items present in the order having confidentiality code greater than Logged-in user at that time the order no# should not display in the first grid.

Table 1:Order
Order_Id     Order_No     Customer_Id
2401          1234567             23
2402         1246001             24
2403          1246002             25

Table 2 : OrderedItems

Table 3: ItemMaster

Item_Id     Item_Name     ConfidentCode
1001          Rice                   Null
1003          Wheet                  7
1005          Badham             Null
1007          Oil                        6
1010          Pista                    8

Out put for 1st grid
Note : Logged-in user have confidentiality code 6

Order No     Customer
1234567          23
1246001          24

3rd order is not displayed in the grid
After user selects the 1st order in the grid then the items present in that 1st order should be displayed as

1001          Rice

the second item not displayed because that having confidentiality code greater than user.

After user selects the 2nd order in the grid then the items present in that order should displays

1005          GroundNut
1007          Oil

I need the query to display the order details in 1st grid.

2

What you need to do is check for the confidence value and also factor in the NULL values

DECLARE @Order TABLE
(
	Order_Id INT,
	Order_No INT,
	Customer_Id INT
)
INSERT INTO @Order
VALUES	(2401,1234567,23),
		(2402,1246001,24),
		(2403,1246002,25);

DECLARE @OrderedItems TABLE
(
OrderItem_ID int,
Order_ID INT,
Item_ID INT,
Sequence INT
)
insert into @OrderedItems
VALUES (1567,2401,1001,1),
(1568,2401,1003,2),
(1569,2402,1005,1),
(1570,2402,1007,2),
(1571,2403,1010,1);

DECLARE @ItemMaster TABLE
(Item_Id INT,Item_Name VARCHAR(25),ConfidentCode INT)
INSERT INTO @ItemMaster
VALUES (1001,'Rice',null),
(1003,'Wheat',7),
(1005,'Badham',Null),
(1007,'Oil',6),
(1010,'Pista',8);

DECLARE @OrderID INT = 2402;

SELECT	*
FROM	@Order AS O
INNER
JOIN	@OrderedItems AS ORD
		ON	O.Order_Id = ORD.Order_ID
INNER
JOIN	@ItemMaster AS I
		ON	ORD.Item_ID = I.Item_Id
WHERE	(I.ConfidentCode <= 6 OR I.ConfidentCode IS NULL)
		AND O.Order_Id = @OrderID;