Home / Questions / 367184
Accepted
Richard Víquez Pérez
Asked: 2020-06-21 13:51:37 +0800 CST 2020-06-21 13:51:37 +0800 CST

How to perform a query without subquery that returns the same result as one with subquery in sql server

  • 772

I have the following query:

SELECT AR.ARTICULO,AR.DESCRIPCION,AP.PRECIO,
              (SELECT AF.FOTO FROM EXSD.ARTICULO_FOTO AF WHERE AF.SECUENCIA = 1 AND AF.ARTICULO = AR.ARTICULO) AS 'FOTO' 
             FROM EXSD.ARTICULO AR JOIN EXSD.ARTICULO_PRECIO AP ON AR.ARTICULO = AP.ARTICULO 
            JOIN EXSD.VERSION_NIVEL VN ON AP.NIVEL_PRECIO = VN.NIVEL_PRECIO 
            AND AP.VERSION = VN.VERSION AND VN.MONEDA = AP.MONEDA 
             WHERE AP.NIVEL_PRECIO = 'ND-LOCAL' AND 
             AP.VERSION = '11' AND AP.MONEDA = 'L' AND VN.SINC_MOVIL = 'S'

Which returns the following result:

enter image description here

And I'm trying to perform that same query but without the subquery :

 SELECT AR.ARTICULO,AR.DESCRIPCION,AP.PRECIO,ARTICULO_FOTO.FOTO
             FROM EXSD.ARTICULO AR JOIN EXSD.ARTICULO_PRECIO AP ON AR.ARTICULO = AP.ARTICULO 
            JOIN EXSD.VERSION_NIVEL VN ON AP.NIVEL_PRECIO = VN.NIVEL_PRECIO 
            AND AP.VERSION = VN.VERSION AND VN.MONEDA = AP.MONEDA 
             join EXSD.ARTICULO_FOTO on 
             ARTICULO_FOTO.SECUENCIA = 1 
             AND ARTICULO_FOTO.ARTICULO = AR.ARTICULO 
             WHERE AP.NIVEL_PRECIO = 'ND-LOCAL' AND 
             AP.VERSION = '11' AND AP.MONEDA = 'L' AND VN.SINC_MOVIL = 'S'

however I am not getting the same result : enter image description here

How can I get the same result of the first query in the second without having to use the subquery?????????

sql

2 Answers

  1. /* PRUEBA CON ESTO, El error en tu query es que FOTO NO ESTAS USAND EL ALIAS AF  */
SELECT 
     AR.ARTICULO
    ,AR.DESCRIPCION
    ,AP.PRECIO
    ,AF.FOTO 
 FROM EXSD.ARTICULO         AR 
 JOIN EXSD.ARTICULO_PRECIO  AP  ON AR.ARTICULO      = AP.ARTICULO 
 JOIN EXSD.VERSION_NIVEL    VN  ON AP.NIVEL_PRECIO  = VN.NIVEL_PRECIO AND AP.VERSION = 
VN.VERSION AND VN.MONEDA = AP.MONEDA 
 JOIN EXSD.ARTICULO_FOTO    AF  ON AF.ARTICULO      = AR.ARTICULO
WHERE AP.NIVEL_PRECIO   = 'ND-LOCAL' 
  AND AP.VERSION        = '11' 
  AND AP.MONEDA         = 'L' 
  AND VN.SINC_MOVIL     = 'S'
  AND AF.SECUENCIA      = 1
    • 0
  2. Best Answer

    I solved it with a Left Join:

    SELECT AR.ARTICULO,AR.DESCRIPCION,AP.PRECIO,AF.FOTO
             FROM EXSD.ARTICULO AR
             JOIN EXSD.ARTICULO_PRECIO AP ON AR.ARTICULO = AP.ARTICULO 
            JOIN EXSD.VERSION_NIVEL VN ON AP.NIVEL_PRECIO = VN.NIVEL_PRECIO 
            AND AP.VERSION = VN.VERSION AND VN.MONEDA = AP.MONEDA 
            LEFT JOIN EXSD.ARTICULO_FOTO AF
            ON AF.ARTICULO=AP.ARTICULO AND AF.SECUENCIA=1
             WHERE AP.NIVEL_PRECIO = 'ND-LOCAL' AND 
             AP.VERSION = '11' AND AP.MONEDA = 'L' AND VN.SINC_MOVIL = 'S'
    • 0