Cross Apply, sub query or something else.... I\'m stuck

  • need help on a query please.

    given the following table;

    CREATE TABLE #T (CATEGORY bigint, NESTEDSETLEFT bigint, NESTEDSETRIGHT BIGINT)
    INSERT INTO #T
    (
    CATEGORY,
    NESTEDSETLEFT,
    NESTEDSETRIGHT
    )
    SELECT 5637145852,1526,1541
    UNION
    SELECT 5637145310,392,407
    UNION
    SELECT 5637145515,814,837

    I need to be able to pass NESTEDSETLEFT and NESTEDSETRIGHT into this query without using a cursor....

    SELECT CATEGORY, ITEMID FROM dbo.SomeTable
    WHERE NESTEDSETLEFT >= @NESTEDSETLEFT
    AND NESTEDSETRIGHT <= @NESTEDSETRIGHT

    thanks in advance.

  • It's not clear what sort of output you are looking for, but how about this?

    SELECT t2.CATEGORY
    ,t2.ITEMID
    FROM dbo.SomeTable t2
    WHERE EXISTS
    (
    SELECT 1
    FROM #T t1
    WHERE t2.NESTEDSETLEFT >= t1.NESTEDSETLEFT
    AND t2.NESTEDSETRIGHT <= t1.NESTEDSETRIGHT
    );

    If that's not what you are after, please describe your requirement more thoroughly.

    In particular, what do you mean by "pass NESTEDSETLEFT and NESTEDSETRIGHT into this query"?


  • I think what I want is this..... but I believe there is an issue with my data.... no surprises there.

     

    SELECT t1.CATEGORY, t1.ITEMID 
    FROM ecomm.Promo_Items t1
    CROSS APPLY (
    SELECT CATEGORY, NESTEDSETLEFT, NESTEDSETRIGHT
    FROM ecomm.Promo_Items
    WHERE CATEGORY IN (select DISTINCT CATEGORY from
    ecomm.Promo_Offers
    where OFFERID = 'TDN000002318') ) a
    WHERE t1.NESTEDSETLEFT >= a.NESTEDSETLEFT
    AND t1.NESTEDSETRIGHT <= a.NESTEDSETRIGHT
  • My head just overheated, trying to understand what your query is trying to do 🙂

    Can you explain, in English, what you are trying to achieve?

    Even better if you can provide a full set of sample DDL, data, and desired results based on that data.

    All SSMS cut-&-paste ready, of course.


  • I think these 3 queries will do the same thing

    SELECT t1.CATEGORY, t1.ITEMID 
    FROM ecomm.Promo_Items t1
    CROSS APPLY (SELECT NESTEDSETLEFT,
    NESTEDSETRIGHT
    FROM ecomm.Promo_Items a
    WHERE CATEGORY IN (select DISTINCT CATEGORY
    from ecomm.Promo_Offers
    where OFFERID = 'TDN000002318')
    AND t1.NESTEDSETLEFT >= a.NESTEDSETLEFT
    AND t1.NESTEDSETRIGHT <= a.NESTEDSETRIGHT
    ) a

     

    ;WITH a AS
    (
    SELECT NESTEDSETLEFT,
    NESTEDSETRIGHT
    FROM ecomm.Promo_Items
    WHERE CATEGORY IN (select DISTINCT CATEGORY
    from ecomm.Promo_Offers
    where OFFERID = 'TDN000002318')
    )
    SELECT t1.CATEGORY, t1.ITEMID
    FROM ecomm.Promo_Items t1
    INNER JOIN a
    ON a.NESTEDSETLEFT >= t1.NESTEDSETLEFT
    AND a.NESTEDSETRIGHT >= t1.NESTEDSETRIGHT
    SELECT t1.CATEGORY, 
    t1.ITEMID
    FROM ecomm.Promo_Items t1
    INNER JOIN (SELECT NESTEDSETLEFT,
    NESTEDSETRIGHT
    FROM ecomm.Promo_Items
    WHERE CATEGORY IN (select DISTINCT CATEGORY
    from ecomm.Promo_Offers
    where OFFERID = 'TDN000002318') a
    ON a.NESTEDSETLEFT >= t1.NESTEDSETLEFT
    AND a.NESTEDSETRIGHT >= t1.NESTEDSETRIGHT

    I think this is the most straight forward and will also give the best performance:

    SELECT t1.CATEGORY, 
    t1.ITEMID
    FROM ecomm.Promo_Items t1
    WHERE EXISTS(SELECT *
    FROM ecomm.Promo_Items a
    WHERE EXISTS(select *
    from ecomm.Promo_Offers b
    where b.OFFERID = 'TDN000002318'
    and b.CATEGORY = a.CATEGORY)
    AND a.NESTEDSETLEFT >= t1.NESTEDSETLEFT
    AND a.NESTEDSETRIGHT >= t1.NESTEDSETRIGHT)

     

  • Thanks Jonathan. The Cross apply actually gives the best performance and the difference between your version and mine are minimal.

    also, FWIW, only the Cross Apply gives the accurate results. I know I didn't give a lot of details, but this discussion was helpful thank you all.

    SET STATISTICS TIME on

    SELECT DISTINCT t1.ITEMID
    FROM ecomm.Promo_Items t1
    CROSS APPLY (SELECT NESTEDSETLEFT,
    NESTEDSETRIGHT
    FROM ecomm.Promo_Items a
    WHERE CATEGORY IN (select DISTINCT CATEGORY
    from ecomm.Promo_Offers
    where OFFERID = 'TDN000002318')
    AND t1.NESTEDSETLEFT >= a.NESTEDSETLEFT
    AND t1.NESTEDSETRIGHT <= a.NESTEDSETRIGHT
    ) a
    WHERE t1.CompanyID = '0010' AND t1.ITEMID IS NOT null


    /*
    (15775 rows affected)

    SQL Server Execution Times:
    CPU time = 108 ms, elapsed time = 167 ms.

    Completion time: 2020-09-24T07:42:56.2508999-04:00

    */


    SELECT DISTINCT t1.ITEMID
    FROM ecomm.Promo_Items t1
    CROSS APPLY (
    SELECT CATEGORY, NESTEDSETLEFT, NESTEDSETRIGHT
    FROM ecomm.Promo_Items
    WHERE CATEGORY IN (select DISTINCT CATEGORY from
    ecomm.Promo_Offers
    where OFFERID = 'TDN000002318') ) a
    WHERE t1.NESTEDSETLEFT >= a.NESTEDSETLEFT
    AND t1.NESTEDSETRIGHT <= a.NESTEDSETRIGHT
    AND t1.CompanyID = '0010' AND t1.ITEMID IS NOT null

    /*
    SQL Server parse and compile time:
    CPU time = 0 ms, elapsed time = 0 ms.

    (15775 rows affected)

    SQL Server Execution Times:
    CPU time = 188 ms, elapsed time = 124 ms.

    Completion time: 2020-09-24T07:43:16.6608797-04:00
    */

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply