In Sql Server while writing queries sometimes we need to the data from multiple tables or you can say that output of one query becomes input to another one.

In this case we will write the nested queries that will get data from the other table and provide it to the another table.

For doing that we need to decide whether the nested query will return single record or multiple records.

If nested query return the single record at every execution time then we can use  operator '=' for this purpose.

We are finding the message from the Nudge table that is made for the location which is in Location table so we are performing the nested query operation for it.

SELECT  N.[ID],
		N.[Message],
		@BeaconID as [BeaconID],
		N.[LocationID],
		L.[Location],
		N.[TargetUserID],
		@UserName as [TargetUser],	
		N.[IsCompleted],
		N.[IsDelivered],
		N.[IsExpired],
		N.[IsDeleted],		
		N.[Url],		
		N.[DaysTillExpire],
		N.[HoursTillExpire],
		(U.[FirstName] + ' ' + U.[LastName]) AS [CreatedBy],
		CONVERT(VARCHAR(20), N.[CreatedDate], 103) as [CreatedDate],
		@CompanyID as [CompanyID]
		
		
		FROM [NUDGE] AS N 
			INNER JOIN  [User] AS U ON N.[CreatedBy]=U.[ID] 
			INNER JOIN [LOCATION] AS L ON L.[ID]=N.[LocationID] 
		WHERE N.[LocationID] = (SELECT [LocationID] FROM [Location] WHERE [BeaconUUID]=@BeaconID and [CompanyID]=@CompanyID AND [BeaconMajor]=@BeaconMajorID AND [BeaconMinor]=@BeaconMinorID)
			AND N.[TargetUserID]=@TargetUserID
			AND N.[IsDeleted]=0 
			AND N.[IsExpired]=0 
			AND N.[IsCompleted]=0 
END

In this case we have fetched the location for the message  that is in the location table   using  '=' operator.  Single location can be returned in this case.
 

But if our nested query return more than one row in any execution then use of this operator will cause an error .

SELECT  N.[ID],
		N.[Message],
		@BeaconID as [BeaconID],
		N.[LocationID],
		L.[Location],
		N.[TargetUserID],
		@UserName as [TargetUser],	
		N.[IsCompleted],
		N.[IsDelivered],
		N.[IsExpired],
		N.[IsDeleted],		
		N.[Url],		
		N.[DaysTillExpire],
		N.[HoursTillExpire],
		(U.[FirstName] + ' ' + U.[LastName]) AS [CreatedBy],
		CONVERT(VARCHAR(20), N.[CreatedDate], 103) as [CreatedDate],
		@CompanyID as [CompanyID]
		
		
		FROM [NUDGE] AS N 
			INNER JOIN  [User] AS U ON N.[CreatedBy]=U.[ID] 
			INNER JOIN [LOCATION] AS L ON L.[ID]=N.[LocationID] 
		WHERE N.[LocationID] IN (SELECT [LocationID] FROM [Location] WHERE [BeaconUUID]=@BeaconID and [CompanyID]=@CompanyID AND [BeaconMajor]=@BeaconMajorID AND [BeaconMinor]=@BeaconMinorID)
			AND N.[TargetUserID]=@TargetUserID
			AND N.[IsDeleted]=0 
			AND N.[IsExpired]=0 
			AND N.[IsCompleted]=0 

So in this case its better to use IN operator instead of '='.

The Nudge table contains the messages for the specific location that is fetched from the Location table so we are fetching it using the Nudge and the Location table. 

We have find the location using the IN operator in this case so multiple location can be returned.