Very Tricky Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Very Tricky Query

Author

Topic

rpc86
Posting Yak Master

200 Posts

Posted - 2009-09-17 : 02:19:07

Hi guys,

For me this is a tricky query and I've been stuck with this for 3 days. This is my query:


Select sa.SubjectAlertKey,obj.ObjectRetrievalGroupFlags,  
COUNT(DISTINCT innersa.SubjectAlertViolationKey) as NoViolations
From ObjectRetrievalGroup obj
Inner Join SubjectAlert sa on sa.SubjectAlertKey= ObjectRetrievalGroupFlags
Left Join  
(

SELECT --- TRICKY QUERY
v.SubjectAlertKey, v.SubjectAlertViolationKey, v.ViolationSubjectKey, 
	v.RuleKey, v.ViolationSubjectTypeKey  FROM SubjectAlertViolation v JOIN SubjectAlert a 
	WITH (NOLOCK) ON a.SubjectAlertKey = v.SubjectAlertKey 
	WHERE ((a.RunDate >= '20010909' AND a.RunDate < '20090917') OR a.RunDate IS NULL)  AND 
	(a.AlertTypeId IN ('RTLR','ELEC','PAPR','COLA','TRAN','TRANAUTH') OR a.AlertTypeId IS NULL) AND
	a.MemberKey = 2) as innersa

on innersa.RuleKey= ObjectRetrievalGroupFlags 

AND 
innersa.ViolationSubjectTypeKey = sa.SubjectAlertKey LEFT JOIN SubjectAlert a 

WITH (NOLOCK) ON a.SubjectAlertKey = innersa.SubjectAlertKey  
where (a.MemberKey =2 OR a.MemberKey IS null)
Group By sa.SubjectAlertKey,obj.ObjectRetrievalGroupFlags

Retuned Data:


SubjectAlertKey ObjectRetrievalGroupFlags NoViolations
--------------- ------------------------- ------------
5               5                         0
7               7                         0
9               9                         0
25              25                        0

Problem: The "NoViolations" column should have values not Zeroes.

Trying to run the Left Join (with remarks "TRICKY QUERY"), set of
data is returned:



SELECT -- TRICKY QUERY
v.SubjectAlertKey, v.SubjectAlertViolationKey, v.ViolationSubjectKey, 
	v.RuleKey, v.ViolationSubjectTypeKey  FROM SubjectAlertViolation v JOIN SubjectAlert a 
	WITH (NOLOCK) ON a.SubjectAlertKey = v.SubjectAlertKey 
	WHERE ((a.RunDate >= '20010909' AND a.RunDate < '20090917') OR a.RunDate IS NULL)  AND 
	(a.AlertTypeId IN ('RTLR','ELEC','PAPR','COLA','TRAN','TRANAUTH') OR a.AlertTypeId IS NULL) AND
	a.MemberKey = 2

Returned Data:


SubjectAlertKey SubjectAlertViolationKey ViolationSubjectKey RuleKey     ViolationSubjectTypeKey
--------------- ------------------------ ------------------- ----------- -----------------------
1               1                        39                  16          3
1               2                        39                  15          3
1               3                        39                  14          3
1               4                        39                  13          3
1               5                        39                  12          3
1               6                        39                  11          3
1               7                        39                  10          3
1               8                        39                  9           3
1               9                        39                  8           3
1               10                       39                  7           3
2               11                       12                  17          3
.... and many more.

What I'm missing on my code?
Why "NoViolation" column has no values where in fact there should be?

Please help.

Thank you.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-17 : 04:41:16

Do you have any data returned for SubjectAlertKey 5,7,9 and 25 from your view? You have only posted non-matching data here. If so, can you post that as an example instead of data that doesn't match?

rpc86
Posting Yak Master

200 Posts

Posted - 2009-09-17 : 05:08:24

Yes, there are 5,7,9 and 25 SubjectAlertKey.

rpc86
Posting Yak Master

200 Posts

Posted - 2009-09-17 : 05:17:21

[code]
SubjectAlertKey SubjectAlertViolationKey ViolationSubjectKey RuleKey ViolationSubjectTypeKey
4 50 13 7 3
5 51 29 24 3
5 52 29 17 3
5 53 29 16 3
5 54 29 15 3
5 55 29 14 3
5 56 29 13 3
5 57 29 12 3
5 58 29 11 3
5 59 29 10 3
5 60 29 9 3
7 87 32 10 2
7 88 32 9 2
7 89 32 8 2
8 90 14 24 3
8 98 14 7 3
9 99 22 15 3
9 100 22 14 3
9 101 22 13 3
9 102 22 10 3
9 103 22 9 3
9 104 22 8 3
9 105 22 7 3
9 106 20 15 3
25 1479 1052 15 3
25 1480 1052 14 3
25 1481 1052 13 3
25 1482 1052 10 3
25 1483 1052 9 3
25 1484 1052 8 3
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-17 : 07:30:40

[code]SELECT sa.SubjectAlertKey,
obj.ObjectRetrievalGroupFlags,
COUNT(DISTINCT innersa.SubjectAlertViolationKey) AS NoViolations
FROM ObjectRetrievalGroup AS obj
INNER JOIN SubjectAlert AS sa on sa.SubjectAlertKey= ObjectRetrievalGroupFlags
LEFT JOIN (
SELECT v.SubjectAlertKey,
v.SubjectAlertViolationKey,
v.ViolationSubjectKey,
v.RuleKey,
v.ViolationSubjectTypeKey
FROM SubjectAlertViolation AS v
INNER JOIN SubjectAlert AS a WITH (NOLOCK) ON a.SubjectAlertKey = v.SubjectAlertKey
AND a.MemberKey = 2
WHERE ((a.RunDate >= '20010909' AND a.RunDate < '20090917') OR a.RunDate IS NULL)
AND (a.AlertTypeId IN ('RTLR', 'ELEC', 'PAPR', 'COLA', 'TRAN', 'TRANAUTH') OR a.AlertTypeId IS NULL)
) AS innersa ON innersa.RuleKey= ObjectRetrievalGroupFlags
AND innersa.ViolationSubjectTypeKey = sa.SubjectAlertKey
LEFT JOIN SubjectAlert AS a WITH (NOLOCK) ON a.SubjectAlertKey = innersa.SubjectAlertKey
AND a.MemberKey = 2
GROUP BY sa.SubjectAlertKey,
obj.ObjectRetrievalGroupFlags[/code]

N 56°04'39.26"
E 12°55'05.63"

rpc86
Posting Yak Master

200 Posts

Posted - 2009-09-17 : 09:27:51

Peso,

Same data returned, Zero "NoViolation"

rpc86
Posting Yak Master

200 Posts

Posted - 2009-09-17 : 23:15:09

Please help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-18 : 02:15:55

Can you post some sample data?
We know the expected output, but do you have original data for us to work with?

N 56°04'39.26"
E 12°55'05.63"

rpc86
Posting Yak Master

200 Posts

Posted - 2009-09-18 : 02:20:20

The given data above are true data.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-18 : 04:06:58

If that is the true data, you should be getting results. Can you post the table structures and some sample data that matches across the tables?

I think you either have data or datatypes that do not match.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources