Condition in Where SQL Server

Question

Condition in Where SQL Server

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I have a SELECT with several LEFT JOIN , however I need to consider all these LEFT JOIN in some conditions, already in another condition I need disregard a made JOIN .

As an example:

SELECT *
      FROM    
              TabelaTotal TT
LEFT JOIN   Client Cli              ON Cli.FKTT      = TT.Id 
LEFT JOIN   Tabela1 CN              ON CN.FK_Cli     = Cli.ID
LEFT JOIN    Produto Pr             ON Tabela1.ID    = Pr.FK_Tabela1
WHERE  
  --Condição considera todos os JOINS   
  and  
  (
   ( CT.Name <> 'A' and 
     Pr.QTD > 0 and  
   ) 
   --Condição que eu preciso desconsiderar o Relacionamento com a Tabela Produto     
    OR 
   ( CT.Name = 'A'
   )
  )
--Como fazer a condição CT.Name = 'A' desconsiderar o LEFT JOIN da tabela Produto
--LEFT JOIN    Produto Pr             ON Tabela1.ID    = Pr.FK_Tabela1

sql sql-server

asked by anonymous 22.03.2016 / 21:11

3 answers

Problems searching php pdo Use the same disk for 2 VMs on Azure

score 1 · Answer 1

In order to disregard de facto JOIN, I can only see the use of UNION . It may seem strange at first, but you can work with two completely different query universes.

And it's not necessarily a bad thing. Sometimes it is simpler to think of operations as union, difference or intersection, as we can associate the problem to be solved with set theory.

Reference: link

Example:

SELECT * FROM    
    TabelaTotal TT
    LEFT JOIN   Client Cli              ON Cli.FKTT      = TT.Id 
    LEFT JOIN   Tabela1 CN              ON CN.FK_Cli     = Cli.ID
    LEFT JOIN    Produto Pr             ON Tabela1.ID    = Pr.FK_Tabela1
WHERE  
( 
    CT.Name <> 'A' and 
    Pr.QTD > 0 and  
) 

UNION   


SELECT * FROM    
    TabelaTotal TT
    LEFT JOIN   Client Cli              ON Cli.FKTT      = TT.Id 
    LEFT JOIN   Tabela1 CN              ON CN.FK_Cli     = Cli.ID 
WHERE
    CT.Name = 'A'

PS: Too many query details have been omitted.

score 0 · Answer 2

I would put the common part in a standard string at design time and add the variable part at runtime.

sqldefault = SELECT * FROM    
TabelaTotal TT
LEFT JOIN   Client Cli              ON Cli.FKTT      = TT.Id 
LEFT JOIN   Tabela1 CN              ON CN.FK_Cli     = Cli.ID
/* variavel1 */ 
WHERE  
( 
   CT.Name <> 'A' 
   /* variavel2 */ 
)

At runtime the program loads the SQLDEFAULT to run.

Verify the condition, if it does not exist execute SQLDEFAULT as is, and follow. Otherwise I change VARIAVEL1 in the string SQLDEFAULT by

LEFT JOIN    Produto Pr             ON Tabela1.ID    = Pr.FK_Tabela1

and the variable2 by

 Pr.QTD > 0

Run and follow.

I need this SQLDEFAULT to be able to clean it in another condition. The system always departs from the default condition and then adds other conditions.

score 0 · Answer 3

You can make use of the EXISTS condition.

SELECT *
FROM TabelaTotal TT
LEFT JOIN Client Cli ON TT.Id = Cli.FKTT
LEFT JOIN Tabela1 CN ON Cli.ID = CN.FK_Cli
WHERE  
    CT.Name = 'A' OR
    EXISTS (SELECT FK_Tabela1 FROM Produto Pr WHERE Pr.FK_Tabela1 = CN.ID AND Pr.QTD > 0)

As for performance, you can look at the following article:

NOT IN vs. NOT EXISTS Vs. LEFT JOIN / IS NULL