December 6, 2015 at 3:58 am
This is My Table
Create table T1 (GoodId int,LocNum int)
Insert into T1(GoodId,LocNum)
Values (1,500)
,(1, 501)
,(1, 502)
,(2, 501)
,(2, 502)
,(3, 500)
,(3, 502)
,(4, 500)
,(4, 501)
,(4, 502)
>>> I want Records that their LocNumbers Have values 500 And 501
Then The result Should be : 1 , 4 (I Dont Want 2 and 3 that have only one of these Values.)
Thank you
December 6, 2015 at 4:07 am
This is one way of doing it:
[Code]
SELECT DISTINCT T1.GoodId
FROM T1 INNER JOIN T1 AS T2 ON T1.GoodId = T2.GoodId
WHERE T1.LocNum = 500 AND T2.LocNum = 501
[/Code]
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 6, 2015 at 4:19 am
😉 Thank you
December 7, 2015 at 4:18 am
More general way to do things like that
select GoodId
from T1
group by GoodId
having count(distinct case when LocNum in (501,502) then LocNum end) = 2
--having count(distinct case when LocNum in (500,501,502) then LocNum end) = 3
December 7, 2015 at 6:41 am
Just for the fun of it, here's another...
IF OBJECT_ID('tempdb..#T1','U') IS NOT NULL
DROP TABLE #T1;
CREATE TABLE #T1 (GoodId INT,LocNum INT);
INSERT#T1 (GoodId,LocNum) VALUES (1,500), (1,501), (1,502), (2,501),
(2,502), (3,500), (3,502), (4,500), (4,501), (4,502);
SELECT
t1.GoodId,
t1.LocNum
FROM
#T1 t1
WHERE
EXISTS (SELECT 1 FROM #T1 t2 WHERE t1.GoodId = t2.GoodId AND t2.LocNum IN (500,501) GROUP BY t2.GoodId HAVING COUNT(*) >= 2)
December 7, 2015 at 10:31 am
Some other ways. The last one is similar to serg's, but should perform better because it should read less rows. However, I can't assure it will perform better without real performance testing.
SELECT GoodId
FROM T1
WHERE LocNum = 500
INTERSECT
SELECT GoodId
FROM T1
WHERE LocNum = 501;
SELECT GoodId
FROM T1
WHERE LocNum IN( 500, 501)
GROUP BY GoodId
HAVING MIN(LocNum) = 500
AND MAX(LocNum) = 501
SELECT GoodId
FROM T1
WHERE LocNum IN( 500, 501)
GROUP BY GoodId
HAVING COUNT(DISTINCT LocNum) = 2
December 8, 2015 at 2:25 am
Luis Cazares (12/7/2015)
Some other ways. The last one is similar to serg's, but should perform better because it should read less rows. However, I can't assure it will perform better without real performance testing.
I thing so. At least I can imagine a setup where your last solution should perform better.
And for some more fun just suppose an input shouldn't be hard-coded but it comes from some table, view or TVF, which is simulated with CTE below.
WITH divider(LocNum,more) AS (
SELECT * FROM (VALUES
(500,''),
(500,'any other data'),
(502,'')
) t(a,b)
)
SELECT GoodId
FROM #T1 t
WHERE LocNum IN (SELECT d.LocNum FROM divider d)
GROUP BY GoodId
HAVING COUNT(DISTINCT t.LocNum) = (SELECT COUNT(DISTINCT d.LocNum) FROM divider d)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply