Adding a count clause to a query

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

SQL Server 2005 Forums

Transact-SQL (2005)

Adding a count clause to a query

Author

Topic

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-05 : 12:06:13

I have the following query where I select records from Active_Activities_temp which do not match on cde_actv in the table ACTIVITY_CORE_LISTING:

SELECT Active_Activities_temp.*
FROM Active_Activities_temp LEFT JOIN 
ACTIVITY_CORE_LISTING ON
Active_Activities_temp.cde_actv=ACTIVITY_CORE_LISTING.cde_actv
WHEREACTIVITY_CORE_LISTING.cde_actv is null
ORDER BY prtcpnt_id

So for example, if a participant has a cde_actv=38 (which doesn't exist in ACTIVITY_CORE_LISTING), that record would appear as the query is currently.

The issue is that participants can have multiple records in Active_Activities_temp and if a participant has a record that does exist in ACTIVITY_CORE_LISTING, no records for that participant should appear in this query result. For example, if a participant has two records in Active_Activities_temp, one with a cde_actv 38 (which does not appear in ACTIVITY_CORE_LISTING) and one with a cde_actv 33 (which does appear in ACTIVITY_CORE_LISTING), no records for that participant should appear in the result. Currently the record with cde_actv=38 does appear.

What code can I implement to do what I need to do? Thanks so much.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-05 : 12:29:36

[code]DECLARE @Table1 TABLE (UserID INT, ActID INT)

INSERT @Table1
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 2, 4

DECLARE @Table2 TABLE (UserID INT, ActID INT)

INSERT @Table2
SELECT 1, 1 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 2, 4

SELECT t1.UserID
FROM @Table1 AS t1
LEFT JOIN @Table2 AS t2 ON t2.UserID = t1.UserID
AND t2.ActID = t1.ActID
GROUP BY t1.UserID
HAVING COUNT(t1.UserID) = COUNT(t2.UserID)

SELECT t1.UserID
FROM @Table1 AS t1
LEFT JOIN @Table2 AS t2 ON t2.UserID = t1.UserID
AND t2.ActID = t1.ActID
GROUP BY t1.UserID
HAVING MAX(CASE WHEN t2.UserID IS NULL THEN 1 ELSE 0 END) = 0[/code]

E 12°55'05.25"
N 56°04'39.16"

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-05 : 12:30:44

Are you looking for this maybe?
SELECT aat.*
FROM Active_Activities_temp aat
WHERE cde_a NOT IN (SELECT cde_a FROM ACTIVITY_CORE_LISTING)
ORDER BY prtcpnt_id

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-05 : 12:48:35

Peso: is there anyway this can be implemented in a view or two rather than a query like that? plus, i have no idea what that query is doing.

yosiasz: that appears to give me the same result as I currently have.

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-05 : 13:26:14

Nevermind. I figured it out. I kept the view that I had above called Placements_wo_Any_Core_Activity. Then I created the following view called Placements_w_Any_Core_Activity:

SELECT Active_Activities_temp.*
FROM Active_Activities_temp LEFT JOIN 
ACTIVITY_CORE_LISTING ON
Active_Activities_temp.cde_actv=ACTIVITY_CORE_LISTING.cde_actv
WHERE ACTIVITY_CORE_LISTING.cde_actv is not null

Then I created a view that is a left joined Placements_wo_Any_Core_Activity and Placements_w_Any_Core_Activity on prtcpnt_id and used the condition "WHERE Placements_w_Any_Core_Activity.prtcpnt_id is null"

:) I just like making things as efficient as possible so I always want to do things in a single view and sometimes I'm not sure if that's possible.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-05 : 14:00:59

Using a single view does not make things efficient. Views have nothing to do with efficiency until you get into partitioned views or indexes views, which based upon what I've seen from your posts is not what is being used.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources