September 23, 2020 at 4:32 pm
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.
September 23, 2020 at 4:52 pm
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"?
September 23, 2020 at 5:08 pm
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
September 23, 2020 at 8:43 pm
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.
September 23, 2020 at 11:39 pm
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)
September 24, 2020 at 11:50 am
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