none
Can i join a table based on If Condition

    Question

  • select * from 
    abc a
    join( if @param=1
        begin
    		SELECT 
    		distinct [Act_ID]   
    		FROM [Asses]
    		where [tobacco]=1
    	end
    	
    	if @param=2
    	begin
    			SELECT 
    			distinct  [Act_ID]   
    			FROM [Asses]
    			where bariatricsupport=1
    	end
    			
    	if @param=3
    	begin
    				SELECT 
    				distinct [Act_ID]   
    				FROM [Asses]
    				where bariatricsurgery=1
    	end
    	  
    	if @param=4
    	begin
    				SELECT 
    				distinct [Act_ID]   
    				FROM [Asses]
    				where stress=1
    	end
    	if @param=5
    	begin
    		SELECT 
    		distinct [Act_ID]   
    		FROM [Asses]
    		where weight=1
        	end 
        	)C on a.Act_ID=c.Act_ID 
    Please let me know if this is possible if not is there any approach better suits my scenario?
    Wednesday, August 21, 2013 10:28 PM

Answers

  • May be ? give a try

    SELECT *
    FROM abc a
    LEFT JOIN [Act_ID] AS c1
    	ON a.Act_ID=c1.Act_ID 
    		AND @param=1 
    		AND c1.[tobacco]=1
    LEFT JOIN  [Act_ID] AS c2
    	ON a.Act_ID=c2.Act_ID 
    		AND @param=2
    		AND c2.bariatricsupport=1
    LEFT JOIN  [Act_ID] AS c3
    	ON a.Act_ID=c3.Act_ID 
    		AND @param=3
    		AND c3.bariatricsurgery=1
    LEFT JOIN  [Act_ID] AS c4
    	ON a.Act_ID=c4.Act_ID 
    		AND @param=4
    		AND c4.stress=1
    LEFT JOIN  [Act_ID] AS c5
    	ON a.Act_ID=c4.Act_ID 
    		AND @param=5
    		AND c5.[weight]=1


    Narsimha


    Wednesday, August 21, 2013 11:26 PM
  • Check this, as no DDL or sample data provided i couldnt test this,

    declare @param int
    select * from 
    abc a
    join
    [Asses] C on a.Act_ID=c.Act_ID 
    where case @param when 1 then [tobacco]
    				  when 2 then bariatricsupport
    				  when 3 then bariatricsurgery
    				  when 4 then stress
    				  when 5 then [weight] end =1
     


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by naveej Thursday, August 22, 2013 2:46 PM
    Thursday, August 22, 2013 12:49 AM
  • Hello,

    IF is a flow command in SQL, you can't use it with a DML statement, like a SELECT.

    But why that complicated, it's just simple Boolean logic, you can solve it this way:

    ;with cte as 
        (SELECT distinct [Act_ID]   
         FROM [Asses]
         where (@param = 1 AND [tobacco]=1)
              OR (@param = 2 AND bariatricsupport=1)
              OR (@param = 3 AND bariatricsurgery=1)
              OR (@param = 4 AND stress=1)
              OR (@param = 5 AND weight=1)
        )
    select * 
    from abc a
         join cte as c
          on a.Act_ID=c.Act_ID 


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    • Proposed as answer by RSingh() Thursday, August 22, 2013 12:10 PM
    • Edited by Olaf HelperMVP Thursday, August 22, 2013 12:39 PM wrong column name
    • Marked as answer by naveej Thursday, August 22, 2013 2:46 PM
    Thursday, August 22, 2013 5:01 AM

All replies

  • May be ? give a try

    SELECT *
    FROM abc a
    LEFT JOIN [Act_ID] AS c1
    	ON a.Act_ID=c1.Act_ID 
    		AND @param=1 
    		AND c1.[tobacco]=1
    LEFT JOIN  [Act_ID] AS c2
    	ON a.Act_ID=c2.Act_ID 
    		AND @param=2
    		AND c2.bariatricsupport=1
    LEFT JOIN  [Act_ID] AS c3
    	ON a.Act_ID=c3.Act_ID 
    		AND @param=3
    		AND c3.bariatricsurgery=1
    LEFT JOIN  [Act_ID] AS c4
    	ON a.Act_ID=c4.Act_ID 
    		AND @param=4
    		AND c4.stress=1
    LEFT JOIN  [Act_ID] AS c5
    	ON a.Act_ID=c4.Act_ID 
    		AND @param=5
    		AND c5.[weight]=1


    Narsimha


    Wednesday, August 21, 2013 11:26 PM
  • Your whole approach to RDBMS and programming in general is completely wrong. There is an old movie where two gangsters are playing cards. One of them says “What do you have?” The other replies “Full house! What about you?” The first gangster says “Gin!” ]

    You are a troll or a cheat trying to get other people to do your job  so you will not be discovered. 

    A JOIN is an operation on tables. A conditional join is like inventing a “should have been” comparison operator to add to equal, less than and greater than. Does that sound absurd when you say it? 

    But even worse, You have no idea how to program outside the RDBMS model! Look up “cohesion” and “coupling” and pay attention the disaster called “flag coupling”? 

    I also has to LMAO at “Asses” as table name. LOL!!

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, August 22, 2013 12:47 AM
  • Check this, as no DDL or sample data provided i couldnt test this,

    declare @param int
    select * from 
    abc a
    join
    [Asses] C on a.Act_ID=c.Act_ID 
    where case @param when 1 then [tobacco]
    				  when 2 then bariatricsupport
    				  when 3 then bariatricsurgery
    				  when 4 then stress
    				  when 5 then [weight] end =1
     


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by naveej Thursday, August 22, 2013 2:46 PM
    Thursday, August 22, 2013 12:49 AM
  • Hello,

    IF is a flow command in SQL, you can't use it with a DML statement, like a SELECT.

    But why that complicated, it's just simple Boolean logic, you can solve it this way:

    ;with cte as 
        (SELECT distinct [Act_ID]   
         FROM [Asses]
         where (@param = 1 AND [tobacco]=1)
              OR (@param = 2 AND bariatricsupport=1)
              OR (@param = 3 AND bariatricsurgery=1)
              OR (@param = 4 AND stress=1)
              OR (@param = 5 AND weight=1)
        )
    select * 
    from abc a
         join cte as c
          on a.Act_ID=c.Act_ID 


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    • Proposed as answer by RSingh() Thursday, August 22, 2013 12:10 PM
    • Edited by Olaf HelperMVP Thursday, August 22, 2013 12:39 PM wrong column name
    • Marked as answer by naveej Thursday, August 22, 2013 2:46 PM
    Thursday, August 22, 2013 5:01 AM
  • Thank you Narsimha, it was really helpful!
    Thursday, August 22, 2013 2:48 PM