July 14, 2015 at 10:48 pm
I have table like this :
declare @tt table
( TagId int, TagType int, ReportId int)
INSERT INTO @tt
( TagId, TagType, ReportId )
VALUES ( 1000,344,1),(71,300,1),(1000,344,2),(71,300,2),(1000,344,3),(71,300,3),(1001,344,4),(71,300,4)
,(71,300,5),(72,301,5),(1000,344,5)
I want to SELECT ReportIds which have (tagid=1000 and tagtype = 344 ) or (tagid=71 and tagtype=300) and dont have any other tagId
this query doesnt works because reportId=5 have (TagId = 72 , TagType = 301) but appears in result !
select ReportId
from @tt
where
(tagid=1000 and tagtype = 344 ) or
(tagid=71 and tagtype=300)
group by ReportId
having count(1) =2
July 14, 2015 at 11:32 pm
Quick suggestion, add a NOT IN clause to exclude the unwanted members
😎
USE tempdb;
GO
SET NOCOUNT ON;
declare @tt table
( TagId int, TagType int, ReportId int)
INSERT INTO @tt
( TagId, TagType, ReportId )
VALUES ( 1000,344,1),(71,300,1),(1000,344,2),(71,300,2),(1000,344,3),(71,300,3),(1001,344,4),(71,300,4)
,(71,300,5),(72,301,5),(1000,344,5);
SELECT DISTINCT
T.ReportId
FROM @tt T
WHERE
(
(T.TagId = 1000 AND T.TagType = 344)
OR
(T.TagId = 71 AND T.TagType = 300)
)
AND T.ReportId NOT IN
(
SELECT
T.ReportId
FROM @tt T
WHERE
(T.TagId NOT IN (1000,71))
AND
(T.TagType NOT IN (300,344))
)
;
Results
ReportId
-----------
1
2
3
4
July 14, 2015 at 11:46 pm
I want to get reports which have both (tagid=1000 and tagtype = 344 ) AND (tagid=71 and tagtype=300) and dont have any other tagid. so this query wont be the answer
SET NOCOUNT ON;
declare @tt table
( TagId int, TagType int, ReportId int)
INSERT INTO @tt
( TagId, TagType, ReportId )
VALUES ( 1000,344,1),(71,300,1),(1000,344,2),(71,300,2),(1000,344,3),(71,300,3),(1001,344,4),(71,300,6)
,(71,300,5),(72,301,5),(1000,344,5);
SELECT DISTINCT
T.ReportId
FROM @tt T
WHERE
(
(T.TagId = 1000 AND T.TagType = 344)
OR
(T.TagId = 71 AND T.TagType = 300)
)
AND T.ReportId NOT IN
(
SELECT
T.ReportId
FROM @tt T
WHERE
(T.TagId NOT IN (1000,71))
AND
(T.TagType NOT IN (300,344))
)
;
ReportId
1
2
3
6
because 6 doesnt have (tagid=1000 and tagtype = 344 )
July 15, 2015 at 12:38 am
farax_x (7/14/2015)
I want to get reports which have both (tagid=1000 and tagtype = 344 ) AND (tagid=71 and tagtype=300) and dont have any other tagid. so this query wont be the answer
SET NOCOUNT ON;
declare @tt table
( TagId int, TagType int, ReportId int)
INSERT INTO @tt
( TagId, TagType, ReportId )
VALUES ( 1000,344,1),(71,300,1),(1000,344,2),(71,300,2),(1000,344,3),(71,300,3),(1001,344,4),(71,300,6)
,(71,300,5),(72,301,5),(1000,344,5);
SELECT DISTINCT
T.ReportId
FROM @tt T
WHERE
(
(T.TagId = 1000 AND T.TagType = 344)
OR
(T.TagId = 71 AND T.TagType = 300)
)
AND T.ReportId NOT IN
(
SELECT
T.ReportId
FROM @tt T
WHERE
(T.TagId NOT IN (1000,71))
AND
(T.TagType NOT IN (300,344))
)
;
ReportId
1
2
3
6
because 6 doesnt have (tagid=1000 and tagtype = 344 )
First a quick advice, try make your questions as complete and accurate as possible in the beginning, piecemeal'ing or moving goal posts doesn't help.
😎
This is not what initially asked which was:
farax_x (7/14/2015)
I want to SELECT ReportIds which have (tagid=1000 and tagtype = 344 ) or (tagid=71 and tagtype=300) and dont have any other tagId
Quick solution
USE tempdb;
GO
SET NOCOUNT ON;
declare @tt table
( TagId int, TagType int, ReportId int)
INSERT INTO @tt
( TagId, TagType, ReportId )
VALUES ( 1000,344,1),(71,300,1),(1000,344,2),(71,300,2),(1000,344,3),(71,300,3),(1001,344,4),(71,300,6)
,(71,300,5),(72,301,5),(1000,344,5);
;WITH MOST_WANTED(TGRP,TagId,TagType) AS
(
SELECT * FROM
(
VALUES (1,1000,344)
,(2,71,300)
) AS X(TGRP,TagId,TagType)
)
,TAGGED_GROUPS AS
(
SELECT
MW.TGRP
,T.ReportId
,T.TagId
,T.TagType
FROM MOST_WANTED MW
INNER JOIN @tt T
ON MW.TagId = T.TagId
AND MW.TagType = T.TagType
)
SELECT
TG.ReportId
FROM TAGGED_GROUPS TG
INNER JOIN TAGGED_GROUPS T2
ON TG.ReportId = T2.ReportId
AND TG.TGRP = T2.TGRP + 1
WHERE TG.ReportId NOT IN
(
SELECT
T.ReportId
FROM @tt T
WHERE
(T.TagId NOT IN (1000,71))
AND
(T.TagType NOT IN (300,344))
)
;
Results
ReportId
-----------
1
2
3
July 15, 2015 at 1:28 am
thank you very much Eirikur but it wont work for this :
declare @tt table
( TagId int, TagType int, ReportId int)
INSERT INTO @tt
( TagId, TagType, ReportId )
VALUES (1000,344,1),
(1000,344,2),
(1000,344,3),
(1000,344,5),
(71,300,1),
(71,300,2),
(71,300,3),
(71,300,4),
(71,300,5),
(71,300,6),
(1001,344,4)
;
;WITH MOST_WANTED(TGRP,TagId,TagType) AS
(
SELECT * FROM
(
VALUES (1,71,300)
) AS X(TGRP,TagId,TagType)
)
,TAGGED_GROUPS AS
(
SELECT
MW.TGRP
,T.ReportId
,T.TagId
,T.TagType
FROM MOST_WANTED MW
INNER JOIN @tt T
ON MW.TagId = T.TagId
AND MW.TagType = T.TagType
)
SELECT
TG.ReportId
FROM TAGGED_GROUPS TG
INNER JOIN TAGGED_GROUPS T2
ON TG.ReportId = T2.ReportId
AND TG.TGRP = T2.TGRP + 1
WHERE TG.ReportId NOT IN
(
SELECT
T.ReportId
FROM @tt T
WHERE
(T.TagId NOT IN (71))
AND
(T.TagType NOT IN (300))
)
;
We expect 6 but it doesnt have result
July 15, 2015 at 1:38 am
Changing the cardinality of the Match Set means having to case the self-join
😎
declare @tt table
( TagId int, TagType int, ReportId int)
INSERT INTO @tt
( TagId, TagType, ReportId )
VALUES (1000,344,1),
(1000,344,2),
(1000,344,3),
(1000,344,5),
(71,300,1),
(71,300,2),
(71,300,3),
(71,300,4),
(71,300,5),
(71,300,6),
(1001,344,4)
;
;WITH MOST_WANTED(TGRP,TagId,TagType) AS
(
SELECT * FROM
(
VALUES (1,71,300)
) AS X(TGRP,TagId,TagType)
)
,GROUP_COUNT AS
(
SELECT COUNT(*) AS GRP_CNT
FROM MOST_WANTED
)
,TAGGED_GROUPS AS
(
SELECT
MW.TGRP
,T.ReportId
,T.TagId
,T.TagType
FROM MOST_WANTED MW
INNER JOIN @tt T
ON MW.TagId = T.TagId
AND MW.TagType = T.TagType
)
SELECT
TG.ReportId
FROM TAGGED_GROUPS TG
CROSS APPLY GROUP_COUNT GC
INNER JOIN TAGGED_GROUPS T2
ON TG.ReportId = T2.ReportId
AND TG.TGRP = CASE WHEN GC.GRP_CNT = 1 THEN 1 ELSE T2.TGRP + 1 END
WHERE TG.ReportId NOT IN
(
SELECT
T.ReportId
FROM @tt T
WHERE
(T.TagId NOT IN (71))
AND
(T.TagType NOT IN (300))
)
;
Results
ReportId
6
July 15, 2015 at 2:00 am
If you can't have duplicate records, then here is another way:
declare @tt table
( TagId int, TagType int, ReportId int)
INSERT INTO @tt
( TagId, TagType, ReportId )
VALUES ( 1000,344,1),(71,300,1),(1000,344,2),(71,300,2),(1000,344,3),(71,300,3),(1001,344,4),(71,300,4)
,(71,300,5),(72,301,5),(1000,344,5),(71,300,6)
;with MyCTE as (
SELECT ReportId, count(*) as NumOfRows
FROM @tt
WHERE ((TagId=1000 and TagType = 344 ) or (TagId=71 and TagType=300))
group by ReportId
having count(*) = 2)
SELECT DISTINCT M.ReportId
FROM MyCTE M
WHERE NOT EXISTS (SELECT * FROM @tt t WHERE (M.ReportId = t.ReportId) AND ((t.TagId <> 1000 AND t.TagId <> 344) AND (t.TagId <> 71 AND t.TagId <> 300)))
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/
July 15, 2015 at 7:04 am
farax_x (7/15/2015)
thank you very much Eirikur but it wont work for this :
declare @tt table
( TagId int, TagType int, ReportId int)
INSERT INTO @tt
( TagId, TagType, ReportId )
VALUES (1000,344,1),
(1000,344,2),
(1000,344,3),
(1000,344,5),
(71,300,1),
(71,300,2),
(71,300,3),
(71,300,4),
(71,300,5),
(71,300,6),
(1001,344,4)
;
;WITH MOST_WANTED(TGRP,TagId,TagType) AS
(
SELECT * FROM
(
VALUES (1,71,300)
) AS X(TGRP,TagId,TagType)
)
,TAGGED_GROUPS AS
(
SELECT
MW.TGRP
,T.ReportId
,T.TagId
,T.TagType
FROM MOST_WANTED MW
INNER JOIN @tt T
ON MW.TagId = T.TagId
AND MW.TagType = T.TagType
)
SELECT
TG.ReportId
FROM TAGGED_GROUPS TG
INNER JOIN TAGGED_GROUPS T2
ON TG.ReportId = T2.ReportId
AND TG.TGRP = T2.TGRP + 1
WHERE TG.ReportId NOT IN
(
SELECT
T.ReportId
FROM @tt T
WHERE
(T.TagId NOT IN (71))
AND
(T.TagType NOT IN (300))
)
;
We expect 6 but it doesnt have result
If you expect 6 based on the originally specified condition, where a given report id has to have both tag combinations, then you are once again changing the conditions. You'll have to decide what the ENTIRE SET OF CONDITIONS are that need to be met before we can provide a working solution. Right now, I don't know what those conditions are. Is it A) "having either of those tag sets or having both", or B) "having both of those tag sets", or C) "there is some other rule we don't know about yet" ? We can't read your mind, so please help us help you and provide ALL the details this time.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply