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.
