none
SubConsulta dentro de campos de Select RRS feed

 > 

  • Pregunta

  • Question
    Inicie sesión para votar
    0
    Inicie sesión para votar

    Hola

    tengo esta query, donde el campo relacional es RestaurantId

    SELECT  A.RestaurantId , A.RestaurantName , A.StreetAddress , A.Phone , CASE WHEN A.Logo = Null OR A.Logo ='logo'
     THEN '../Delivery/img/avatar2.jpg' ELSE ('https://sabor.blob.core.windows.net/restaurantslogo/' + A.Logo + '.png')  END Logo ,
FORMAT(CONVERT(DATETIME, B.HoursEnd), 'hh:mm:tt') AS HoursEnd  
FROM Restaurant A , RestaurantHours B , DeliveryConfiguration C WHERE A.RestaurantId=B.RestaurantId  AND
     A.RestaurantId = C.RestaurantId AND WeekDay = DATENAME(WeekDay, GETDATE()) AND B.HoursType='Delivery'
AND C.Position.STDistance(@position) IS NOT NULL  AND C.Position.STDistance(@position) <= 5000;

    ahora quiero agregar  la siguiente como un campo de la primera  fijense que la selecciono segun restaurantid

    select MIN(B.PRICE) FROM Items A , Priceitem B
     WHERE A.RestaurantID =@restaurantid AND A.ItemID= B.ItemId AND price <> 0;

    EFRAIN MEJIAS C VALENCIA - VENEZUELA

    martes, 18 de octubre de 2016 23:14
    |

Respuestas

  • Question
    Inicie sesión para votar
    2
    Inicie sesión para votar

    Efrain Mejias Castillo,

    WITH CTE AS 
(
    SELECT 
	   A.RestaurantID,
	   MIN(B.price) Price
    FROM 
	   Items A
	   INNER JOIN Priceitem B ON A.ItemID = B.ItemId
		  AND B.price <> 0
    GROUP BY
	   A.RestaurantID
)
SELECT  
    ISNULL(D.Price, 0),
    A.RestaurantId,
    A.RestaurantName,
    A.StreetAddress,
    A.Phone,
    CASE
	   WHEN A.Logo IS NULL OR A.Logo ='logo' THEN '../Delivery/img/avatar2.jpg'
	   ELSE CONCAT('https://sabor.blob.core.windows.net/restaurantslogo/', A.Logo, '.png')
    END Logo,
    FORMAT(CONVERT(DATETIME, B.HoursEnd), 'hh:mm:tt') AS HoursEnd
FROM 
    Restaurant A
    INNER JOIN RestaurantHours B ON A.RestaurantId = B.RestaurantId
	   AND B.HoursType = 'Delivery'
    INNER JOIN DeliveryConfiguration C ON A.RestaurantId = C.RestaurantId 
	   AND C.Position.STDistance(@position) IS NOT NULL  
	   AND C.Position.STDistance(@position) <= 5000
    LEFT JOIN CTE D ON (A.RestaurantID = D.RestaurantID)
WHERE 
    WeekDay = DATENAME(WeekDay, GETDATE());

    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    martes, 18 de octubre de 2016 23:31
    |