this is easy... right? - 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

SQL Server 2000 Forums

Transact-SQL (2000)

this is easy... right?

Author

Topic

Danny__T
Starting Member

27 Posts

Posted - 2004-09-16 : 08:06:15

All I want to do is count the number of occurances of an Id in a joined table e.g.

table1
ID
field1
field2

table2
ID
table1ID*
fielda
fieldb

I'm currently using

SELECT table1.field1, table2.field2, (SELECT COUNT(table2.table1Id) FROM table1 INNER JOIN table2.table1ID ON table1.ID) AS t1count

But that just returns the total number of table1ID occurances rather than just the number of each records occurances (why are these things always so hard to explain?).

So what I wish to return is:

field1 field2 t1count
abc xyz 3
def pqr 2

but all i'm getting is
field1 field2 t1count
abc xyz 5
def pqr 5

clear as mud? good, if it makes sense i'd really appreciate your help.

Cheers

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-16 : 08:10:57

will this do??

SELECT field1, field2, t1Count = (SELECT COUNT(table1Id) FROM table1 where table1Id= t2.table2Id)
FROM table2 t2

Go with the flow & have fun! Else fight the flow