SQL do inner join if condition met

Question

i want a good way to improve my sql code, i have to use inner join when condition is met. I am currently replicates the code:

@SystemMerge bit

if (@SystemMerge=1)  
BEGIN
   SELECT
         .......
      FROM myTable
      INNER JOIN table ON table.param1=myTable.param1
      INNER JOIN systemTable on systemTable.param2=myTable.param2
   END
ELSE
   BEGIN
      SELECT
         .......
      FROM myTable
      INNER JOIN table ON table.param1=myTable.param1
   END

and i would like to do it in a way like this:

@SystemMerge bit
BEGIN
   SELECT
      .......
   FROM myTable
   INNER JOIN table ON table.param1=myTable.param1
   ***//the next 4 lines is not working, but this pseudo of what i want:***
   if (@SystemMerge=1)  
   begin
      INNER JOIN systemTable on systemTable.param2=myTable.param2
   end

edit: the solution (thanks to @Damien_The_Unbeliever):

LEFT JOIN systemTable ON systemTable.param2=myTable.param2
WHERE 
   ((@SystemMerge=1 AND systemTable.param2 is not null) 
   OR
   (@SystemMerge=0 OR @SystemMerge is null))

show us the code that isn't working. why isn't it working whats the error your getting? maybe an example of some data — Dev N00B, Jul 24 '13 at 07:53
You can build dynamic query based on condition and execute that query later with EXEC(query). — Akash KC, Jul 24 '13 at 07:56
dynamic query example : http://www.techrepublic.com/blog/the-enterprise-cloud/generate-dynamic-sql-statements-in-sql-server/ — Graham Griffiths, Jul 24 '13 at 07:57
C'Mon, is it so hard to do a search for "Conditional Inner Join"? google will give you e.g. http://stackoverflow.com/questions/7268604/conditional-inner-join — Ocaso Protal, Jul 24 '13 at 07:59

score 20 · Accepted Answer · answered Jul 24 '13 at 07:57

20

This should (approxmately) do the same thing:

SELECT
     .......
  FROM myTable
  INNER JOIN table ON table.param1=myTable.param1
  LEFT JOIN systemTable on systemTable.param2=myTable.param2 and @SystemMerge = 1
  WHERE (@SystemMerge = 0 OR systemTable.NonNullableColumn IS NOT NULL)

Of course, this also means that any other references to columns within systemTable must be written to expect such columns to be NULL.

answered Jul 24 '13 at 07:57

Damien_The_Unbeliever

234,701
27
340
448

not helping in my case but i keep your answer for future needs. thanks. – YaakovHatam Jul 24 '13 at 08:58
OK, i have improved the answer and now it works good. thanks. picked you. – YaakovHatam Jul 24 '13 at 09:41

score 5 · Answer 2 · answered Jul 24 '13 at 09:14

How about dynamic sql?

declare @sel varchar(max)

set @sel = ' SELECT
         .......
         FROM myTable
         INNER JOIN table ON table.param1=myTable.param1
        '

 if (@SystemMerge=1)  
   begin
     set @sel = @sel+'INNER JOIN systemTable on systemTable.param2=myTable.param2'
   end

exec(@sel)

score 2 · Answer 3 · edited May 01 '18 at 05:04

2

Simple way if I am right -

SELECT
    .......
FROM myTable
INNER JOIN table ON table.param1 = myTable.param1
INNER JOIN systemTable on @SystemMerge = 0 or systemTable.param2=myTable.param2

edited May 01 '18 at 05:04

ikaikastine

601
8
22

answered Apr 25 '18 at 08:57

Arun Gupta

21
1

SQL do inner join if condition met

3 Answers3

Linked