how get assing farmer according user id [Resolved]

Posted by Cpatil1000 under Sql Server on 8/9/2016 | Points: 10 | Views : 282 | Status : [Member] | Replies : 1
Hi,

I a trying to display inforation according to user passsing id. @USER_ID is my user id.
If i passed @USER_ID = 1 then display all record of farmer is user has not assin
the farmer because user id 1 is admin. and if passed user id 8 or 9 or other no. then it user id
which has assign in assign table.
My query is not getting proper result.


FARMER_MASTER (TABLE)
ID	NAME
13 Farmer 02
14 Farmer 03
15 Farmer 04
16 Farmer 05
17 Farmer 06
18 FARMER
19 FARMER TEST



ASSIGN_FARMER (TABLE)

FARMER_ID	ASSIGNED_TO
13 8
14 8
15 9
16 9
18 9
17 8



Declare @USER_ID int
Set @USER_ID = 18


Select FM.ID, FM.NAME  From FARMER_MASTER FM
Left Outer Join ASSIGN_FARMER ANM On ANM.FARMER_ID = FM.ID
And ANM.ASSIGNED_TO = (Case When @USER_ID = 1 Then ANM.ASSIGNED_TO Else @USER_ID End)





Responses

Posted by: Rajnilari2015 on: 8/9/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
A simple IF ELSE gives you the correct output (:

DECLARE @FARMER_MASTER TABLE(ID INT, NAME VARCHAR(20))
INSERT INTO @FARMER_MASTER VALUES(13,'Farmer 02'),(14,'Farmer 03'),(15,'Farmer 04'),(16,'Farmer 05'),(17,'Farmer 06'),(18,'FARMER'),(19,'FARMER TEST')

DECLARE @ASSIGN_FARMER TABLE(FARMER_ID INT, ASSIGNED_TO INT)
INSERT INTO @ASSIGN_FARMER VALUES(13,8),(14,8),(15,9),(16,9),(18,9),(17,8)

DECLARE @USER_ID INT = 1

IF ( @USER_ID = 1 )

BEGIN

SELECT FM.ID, FM.NAME
FROM @FARMER_MASTER FM

END

ELSE

BEGIN

SELECT
FM.ID, FM.NAME
FROM @FARMER_MASTER FM
WHERE FM.ID IN (SELECT AF.FARMER_ID FROM @ASSIGN_FARMER AF WHERE AF.ASSIGNED_TO = @USER_ID)

END


--
Thanks & Regards,
RNA Team

Cpatil1000, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response