Write conditional INNER JOIN in SQL Server Query

Last Reply on Jul 12, 2015 10:55 PM By Shubh4m

Posted on Jul 09, 2015 03:34 AM

Hi

I am having two tables and i am passing particular value. I need to put inner join based on that value. If the value is empty, no inner join else i need to perform inner join. I am not preferring dynamic query.

Kindly let me know is there any other way to achive this.

Thanks

Posted on Jul 09, 2015 06:41 AM

If you don’t want to use dynamic query then you always need to check Filtervalue condition either it may be NULL or '1' else it may '2'. See the Below Sample test query 

 

 

DECLARE @TABLE AS TABLE (id int, Name VARCHAR(50))
DECLARE @TABLE1 AS TABLE (id int, Class VARCHAR(50))
DECLARE @TABLE2 AS TABLE (id int, Books VARCHAR(100))
DECLARE @FilterValue CHAR(1)

/*DEPENDING ON FILTER VALUE IT WILL FILTER RECORDS */
SET  @FilterValue  = NULL
--SET @FilterValue  = '1'
--SET @FilterValue  = '2'

INSERT INTO @TABLE VALUES (1,'Vikas')
				  ,(2,'Vinayak')
				  ,(3,'Prashant')
				  ,(4,'Bhavesh')
				  ,(5,'Jay')
				  ,(6,'Avinash')

INSERT INTO @TABLE1 VALUES (1,'F.Y.Bsc IT')
				  ,(2,'F.Y.Bsc CS')				  
				  ,(5,'F.Y.Bsc Chem')
				  ,(6,'F.Y.Bsc Maths')


INSERT INTO @TABLE2 VALUES (1,'Let Us C')
						   ,(1,'C++')				  
						   ,(2,'SQL SERVER')

IF (@FilterValue IS NULL)
BEGIN

	SELECT Id , Name
	FROM @TABLE
END
ELSE
BEGIN
	IF @FilterValue = '1'
		BEGIN
				SELECT T.Id 
					   ,Name
					   ,T1.Class
				FROM @TABLE T
				INNER JOIN @TABLE1 T1
				ON T.id = T1.id
		END
	ELSE
		BEGIN
				SELECT T.Id 
					   ,Name
					   ,T2.Books
				FROM @TABLE T
				INNER JOIN @TABLE2 T2
				ON T.id = T2.id
		END
END

 


Posted on Jul 12, 2015 10:55 PM

Dynamic SQL // :

DECLARE @Query1 varchar(max),@Query2 varchar(max), @Variable varchar(20),@Flag varchar(20)
SET @Variable = '''TEST'''
SET @Flag = 'asd'
SET @Query1 = 'SELECT * FROM DATA WHERE VOUCHERNO ='+@Variable
SET @Query2 = 'SELECT * FROM DATA D INNER JOIN MR000 M ON D.BookNo = M.MasterID WHERE VOUCHERNO ='+@Variable

BEGIN
IF(@Flag<>@Variable)
	BEGIN
		EXEC(@Query2)
	END
ELSE
	BEGIN 
		EXEC(@Query1)
	END
END

 

This Might Help!