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"?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply