Condition in Where SQL Server

Asked

Viewed 672 times

0

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

As an example below:

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

  • LEFT JOIN already allows you to disregard the relationship! Do you want to pick only where the relationship is not valid? That’s it?

  • I want to make the relationship with the Product table only as CT.Name <> 'A' Case CT.Name = 'A' I want to make the same select but I want to disregard the relationship with the Product table, using the LEFT it returns values, I want not even make the relationship with Product

  • First of all, I don’t see any tables renamed as CT, is not clear the question, its LEFT JOIN return all data from these tables and your WHERE filter where (CT.Name <> 'A' and Pr.QTD > 0 and ) or (CT.Name = 'A') , what problem here?

3 answers

1

For you to disregard in fact JOIN, I can only see the use of UNION. It may seem strange at first, but you can work with two completely different universes in terms of query.

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

Reference

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: Other query details have been omitted.

0

I would put the common part in a standard string at design time and add or not 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.

Checking the condition, if it does not exist executes the SQLDEFAULT as is and follows. Otherwise I change the VARIAVEL1 in the SQLDEFAULT string by 

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

and the variable2 by 

 Pr.QTD > 0

executes and follows.

I need this SQLDEFAULT to be able to clean it in another condition. The system always starts from the standard condition and then adds or not other conditions.

0

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

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.