none
SubConsulta dentro de campos de Select RRS feed

  • Pregunta

  • 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

  • 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