how get assing farmer according user id [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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)

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts