none
Query com resultado duplicados RRS feed

  • Pergunta

  • Olá, estou batendo a cabeça aqui, não sei onde estou errando, ainda estou aprendendo então qualquer ajuda será bem vinda.

    Tabela MUser

       | MUser_USER_NUMBER | MUser_USER_NAME |
       ---------------------------------------
       |        1          |     User1       |

    Tabela MPassport

       | MPassport_USER_NUMBER| MPassport_NUMBER  | MPassport_EXPIRE_DATE |
       ----------------------------------------------------------------------
       |        1             |     12345678      |     2021-01-28        |

    Tabela MCar

       | MCar_USER_NUMBER  | MCar_PLATE  | MCar_SHAKEN_EXPIRE_DATE | MCar_JIBAISEKI_EXPIRE_DATE | MCar_INSURANCE_EXPIRE_DATE |
       -----------------------------------------------------------------------------------------------------------------------
       |        1          |  Plate 1    |      2020-12-07         |         2020-12-08         |         2021-07-01         | 
       |        1          |  Plate 2    |      2021-03-01         |         2021-03-02         |         2021-01-01         | 

    Tabela MDrive

       | MDrive_USER_NUMBER| MDrive_NUMBER  | MDrive_EXPIRE_DATE |
       ----------------------------------------------------------------------
       |        1          |    12345678    |    2022-10-28      |

    Resultado Esperado

       | MCar_USER_NUMBER  | MUser_USER_NAME  | MPassport_EXPIRE_DATE | MDrive_EXPIRE_DATE | MCar_PLATE  | MCar_SHAKEN_EXPIRE_DATE | MCar_JIBAISEKI_EXPIRE_DATE | MCar_INSURANCE_EXPIRE_DATE |
       --------------------------------------------------------------------------------------------------------------------------------------------------------------
       |        1          |      User1       |       2021-01-28      |                    |  Plate 1    |      2020-12-07         |         2020-12-08         |                            | 
       |        1          |      User1       |       2021-01-28      |                    |  Plate 2    |                         |                            |         2021-01-01         | 

    Query usada

    SELECT MUser.MUser_USER_NUMBER As MUser_USER_NUMBER      
          ,MUser.MUser_USER_NAME AS MUser_USER_NAME       
          ,(SELECT ISNULL(MIN(Mpassport.Mpassport_EXPIRE_DATE), NULL)      
              FROM MPassPort          
             WHERE MPassport.Mpassport_EXPIRE_DATE < '2021/02/16'            
               AND MPassport.Mpassport_EXPIRE_DATE > '1900/01/01'            
               AND MPassport.Mpassport_USER_NUMBER = MUser_USER_NUMBER ) As Mpassport_EXPIRE_DATE                
          ,(SELECT ISNULL(MIN(MDrive.MDrive_EXPIRE_DATE), NULL)           
              FROM MDrive          
             WHERE MDrive.MDrive_EXPIRE_DATE < '2021/02/16'            
               AND MDrive.MDrive_EXPIRE_DATE > '1900/01/01'            
               AND MDrive.MDrive_USER_NUMBER = MUser_USER_NUMBER ) As MDrive_EXPIRE_DATE  
          ,MCar.MCar_PLATE AS MCar_PLATE      
          ,(SELECT ISNULL(MIN(MCar.MCar_SHAKEN_EXPIRE_DATE), NULL)          
              FROM MCar          
             WHERE MCar.MCar_SHAKEN_EXPIRE_DATE < '2021/02/16'            
               AND MCar.MCar_SHAKEN_EXPIRE_DATE > '1900/01/01'            
               AND MCar.MCar_USER_NUMBER = MUser_USER_NUMBER ) As MCar_SHAKEN_EXPIRE_DATE       
          ,(SELECT ISNULL(MIN(MCar.MCar_JIBAISEKI_EXPIRE_DATE), NULL)           
              FROM MCar         
             WHERE MCar.MCar_JIBAISEKI_EXPIRE_DATE < '2021/02/16'            
               AND MCar.MCar_JIBAISEKI_EXPIRE_DATE > '1900/01/01'            
               AND MCar.MCar_USER_NUMBER = MUser_USER_NUMBER ) As MCar_JIBAISEKI_EXPIRE_DATE       
          ,(SELECT ISNULL(MIN(MCar.MCar_INSURANCE_EXPIRE_DATE), NULL)            
              FROM MCar          
             WHERE MCar.MCar_INSURANCE_EXPIRE_DATE < '2021/02/16'            
               AND MCar.MCar_INSURANCE_EXPIRE_DATE > '1900/01/01'            
               AND MCar.MCar_USER_NUMBER = MUser_USER_NUMBER ) As MCar_INSURANCE_EXPIRE_DATE   
      FROM (((MUser    
      LEFT JOIN MPassport On MUser.MUser_USER_NUMBER = MPassport.Mpassport_USER_NUMBER)                         
      LEFT JOIN MDrive On  MUser_USER_NUMBER = MDrive.MDrive_USER_NUMBER)               
      LEFT JOIN MCar On  MUser_USER_NUMBER = MCar.MCar_USER_NUMBER)  
     WHERE (SELECT Mpassport_EXPIRE_DATE           
              FROM MPassPort          
             WHERE Mpassport_EXPIRE_DATE < '2021/02/16'            
               AND Mpassport_EXPIRE_DATE > '1900/01/01'            
               AND Mpassport_USER_NUMBER = MUser_USER_NUMBER) < '2021/02/16'
        OR (SELECT MDrive_EXPIRE_DATE           
              FROM MDrive          
             WHERE MDrive_EXPIRE_DATE < '2021/02/16'            
               AND MDrive_EXPIRE_DATE > '1900/01/01'            
               AND MDrive_USER_NUMBER = MUser_USER_NUMBER) < '2021/02/16'     
        OR (SELECT MCar_SHAKEN_EXPIRE_DATE           
              FROM MCar          
             WHERE MCar_SHAKEN_EXPIRE_DATE < '2021/02/16'            
               AND MCar_SHAKEN_EXPIRE_DATE > '1900/01/01'            
               AND MCar_USER_NUMBER = MUser_USER_NUMBER) < '2021/02/16'     
        OR (SELECT MCar_JIBAISEKI_EXPIRE_DATE           
              FROM MCar          
             WHERE MCar_JIBAISEKI_EXPIRE_DATE < '2021/02/16'            
               AND MCar_JIBAISEKI_EXPIRE_DATE > '1900/01/01'            
               AND MCar_USER_NUMBER = MUser_USER_NUMBER) < '2021/02/16'     
        OR (SELECT MCar_INSURANCE_EXPIRE_DATE           
              FROM MCar          
             WHERE MCar_INSURANCE_EXPIRE_DATE < '2021/02/16'            
               AND MCar_INSURANCE_EXPIRE_DATE > '1900/01/01'            
               AND MCar_USER_NUMBER = MUser_USER_NUMBER) < '2021/02/16'

    Resultado recebido

       | MCar_USER_NUMBER  | MUser_USER_NAME  | MPassport_EXPIRE_DATE | MDrive_EXPIRE_DATE | MCar_PLATE  | MCar_SHAKEN_EXPIRE_DATE | MCar_JIBAISEKI_EXPIRE_DATE | MCar_INSURANCE_EXPIRE_DATE |
       --------------------------------------------------------------------------------------------------------------------------------------------------------------
       |        1          |      User1       |       2021-01-28      |                    |  Plate 1    |      2020-12-07         |         2020-12-08         |         2021-01-01         | 
       |        1          |      User1       |       2021-01-28      |                    |  Plate 2    |      2020-12-07         |         2020-12-08         |         2021-01-01         | 

    Alguma alma caridosa pode ajudar um novato que está perdido?

    Essa query na verdade ainda tem mais coisa, mas se conseguir funcionar isso o resto seria somente adicionar as outras tabelas.

    De início muito obrigado pela paciencia de ler tudo isso.


    • Editado kristianmsf sábado, 21 de novembro de 2020 03:50 corrigir data
    quinta-feira, 19 de novembro de 2020 06:29

Respostas

  • Olá, me ajudaram em outro lugar, então venho aqui deixar a solução para que se alguém mais precisar tenha aqui para ler. 

    Obrigado.

     

    SELECT 

      MUser.MUser_USER_NUMBER,
      MUser.MUser_USER_NAME,    
      CASE WHEN 
        Mpassport.Mpassport_EXPIRE_DATE < '2021/02/16' AND 
        Mpassport.Mpassport_EXPIRE_DATE > '1900/01/01'            
          THEN Mpassport.Mpassport_EXPIRE_DATE 
      END As Mpassport_EXPIRE_DATE,
        MCar.MCar_PLATE,
      CASE WHEN 
        MCar.MCar_SHAKEN_EXPIRE_DATE < '2021/02/16' AND 
        MCar.MCar_SHAKEN_EXPIRE_DATE > '1900/01/01'            
          THEN MCar.MCar_SHAKEN_EXPIRE_DATE 
      END As MCar_SHAKEN_EXPIRE_DATE,
      CASE WHEN
        MCar.MCar_JIBAISEKI_EXPIRE_DATE < '2021/02/16' AND
        MCar.MCar_JIBAISEKI_EXPIRE_DATE > '1900/01/01'
          THEN MCar.MCar_JIBAISEKI_EXPIRE_DATE
      END As MCar_JIBAISEKI_EXPIRE_DATE,
      CASE WHEN 
        MCar.MCar_INSURANCE_EXPIRE_DATE < '2021/02/16' AND
        MCar.MCar_INSURANCE_EXPIRE_DATE > '1900/01/01'
          THEN MCar.MCar_INSURANCE_EXPIRE_DATE
      END As MCar_INSURANCE_EXPIRE_DATE   
    FROM MUser    
    LEFT JOIN MPassport 
      On 
        MPassport.Mpassport_USER_NUMBER = MUser.MUser_USER_NUMBER AND
        MPassport.Mpassport_EXPIRE_DATE < '2021/02/16' AND 
        MPassport.Mpassport_EXPIRE_DATE > '1900/01/01'    
    LEFT JOIN MDrive 
      On
        MDrive.MDrive_USER_NUMBER = MUser_USER_NUMBER AND
        MDrive.MDrive_EXPIRE_DATE < '2021/02/16' AND
        MDrive.MDrive_EXPIRE_DATE > '1900/01/01'
    LEFT JOIN MCar 
      On
        MCar.MCar_USER_NUMBER = MUser_USER_NUMBER AND
        ( (MCar_SHAKEN_EXPIRE_DATE < '2021/02/16' AND
           MCar_SHAKEN_EXPIRE_DATE > '1900/01/01') 
         OR
          (MCar_JIBAISEKI_EXPIRE_DATE < '2021/02/16' AND
           MCar_JIBAISEKI_EXPIRE_DATE > '1900/01/01') 
         OR
          (MCar_INSURANCE_EXPIRE_DATE < '2021/02/16' AND 
           MCar_INSURANCE_EXPIRE_DATE > '1900/01/01') )
    WHERE
      MPassport_EXPIRE_DATE IS NOT NULL OR
      MDrive_EXPIRE_DATE IS NOT NULL OR
      MCar_SHAKEN_EXPIRE_DATE IS NOT NULL OR
      MCar_JIBAISEKI_EXPIRE_DATE IS NOT NULL OR
      MCar_INSURANCE_EXPIRE_DATE IS NOT NULL


    • Marcado como Resposta kristianmsf terça-feira, 24 de novembro de 2020 01:06
    terça-feira, 24 de novembro de 2020 01:06