Count If record has link...How to

[Mmattson]

I have a SQL 2005 DB with 57,000 accounts and a huge quantity of activities (emails, letters, phone call, etc.) related to the Accounts. I want to find the accounts where there is at least 1 activity...not the number of activities. You can imagine the time it takes to pull this data!

Tbl#1 - Accounts
ID
f1
f2
f3


Tbl#2 - Activities
ID
AccountID
fA
fB
fC

What should my join statement and query look like to get a simple true/false if there is a link?

 

[MSAccessRookie]

I have a SQL 2005 DB with 57,000 accounts and a huge quantity of activities (emails, letters, phone call, etc.) related to the Accounts. I want to find the accounts where there is at least 1 activity...not the number of activities. You can imagine the time it takes to pull this data!

Tbl#1 - Accounts
ID
f1
f2
f3


Tbl#2 - Activities
ID
AccountID
fA
fB
fC

What should my join statement and query look like to get a simple true/false if there is a link?

I am not sure if this will work, but it looks like it might
Select Accounts.ID
From Accounts Inner Join Activities
On Accounts.ID=Activities.AccountID
Group By Accounts.ID Having Count(Activities.ID) > 0

 

[Mmattson]

OK, but won't the GROUP BY still try to count all of the activities? It is >1million records.

 

[MSAccessRookie]

OK, but won't the GROUP BY still try to count all of the activities? It is >1million records.

The Group By statement will count the records
The Having statement should exclude the ones that you do not want to see

 

[SQL_Hell]

OK, but won't the GROUP BY still try to count all of the activities? It is >1million records.

Yes i think it will still count the activity records, try this:

Select a.ID
From Accounts a
where a.id in (select accountid from activities where a.id = accountid)
Group By a.ID

 

[Mmattson]

I'll give that a try. Thanks!