none
Duvida - Select RRS feed

  • Pergunta

  • SELECT  c.idCustomer, c.dsEmail, c.dsPassword, c.dsPwdCrypto, c.dsName, c.dsNickName, c.idProfile, c.dtCreated, c.dtLastLogin, c.dtLastOrder, c.fgReceiveEmail, c.fgActiveLogin, c.fgOldCustomer, c.fgReceiveEmailFormat,        
      c.idVipCustomerMain, c.dsOtherReference, a.dsZip, p.idPriceTable, p.idCatalog, c.dtModified,        
      fgVipCustomer = isnull(CASE when (convert(char(8),dateadd(m,1,dateadd(d,-datepart(d, v.dtExpiration),v.dtExpiration)),112) >= convert(char(8), getdate(), 112)) then 1 end,0)        
        FROM tbCustomer c, tbProfile p, tbCustomerDeliveryAddr a, tbVipCustomer v        
        WHERE c.dsEmail = 'teste@hotmail.com' AND c.idProfile = p.idProfile        
      AND c.idCustomer = a.idCustomer and a.fgCustomerAddress = 1        
      AND v.idCustomer =* c.idCustomer AND v.idVipCustomer =* c.idVipCustomerMain   

    Pessoal, como ficaria a consulta acima ? O certo seria usar Right ou Left Join nesse caso?

    obrigado

    terça-feira, 18 de dezembro de 2012 16:49

Todas as Respostas

  • Pessoal, eu fiz da seguinte maneira.. Mas parece que não traz o resultado esperado...

    SELECT  c.idCustomer, c.dsEmail, c.dsPassword, c.dsPwdCrypto, c.dsName, c.dsNickName, c.idProfile, c.dtCreated, c.dtLastLogin, c.dtLastOrder, c.fgReceiveEmail, c.fgActiveLogin, c.fgOldCustomer, c.fgReceiveEmailFormat,        
      c.idVipCustomerMain, c.dsOtherReference, a.dsZip, p.idPriceTable, p.idCatalog, c.dtModified,        
      fgVipCustomer = isnull(CASE when (convert(char(8),dateadd(m,1,dateadd(d,-datepart(d, v.dtExpiration),v.dtExpiration)),112) >= convert(char(8), getdate(), 112) ) then 1 end,0)        
        FROM tbCustomer c  
        INNER JOIN tbProfile p ON c.idProfile = p.idProfile    
        INNER JOIN tbCustomerDeliveryAddr a ON c.idCustomer = a.idCustomer  
        RIGHT JOIN tbVipCustomer v  ON v.idCustomer = c.idCustomer
                                   AND v.idVipCustomer = c.idVipCustomerMain 
        WHERE c.dsEmail = 'teste@teste.com.br'  
         and a.fgCustomerAddress = 1    

    terça-feira, 18 de dezembro de 2012 17:44