SQL Subquery Count Problem 1

[waiterm]

Hi,

I'm trying to join two tables, whereby the first table has unique records, the second table then has multiple records assigned against the primary key of the first table.

Basically I'm looking for a query which lists the top 20 records from the first table with an extra field stating the number of records associated to it in the second field.

i.e.

Table 1
T1PK
1 Field1_1 Field1_2 Field1_3
2 Field2_1 Field2_2 Field2_3
3 Field3_1 Field3_2 Field3_3

Table 2
T2PK T1PK
1 Field1_1 1
2 Field2_1 1
3 Field3_1 3

Results
T1PK Count
1 Field1_1 Field1_2 Field1_3 2
2 Field2_1 Field2_2 Field2_3 0
3 Field3_1 Field3_2 Field3_3 1

Having racked my brains for ages and got nowhere any help would be gratefully appreciated.


Rob Waite

http://www.hostpipe.co.uk?src=TT

 

[r937]

select top 20
       T1PK
     , Fieldx_1  
     , Fieldx_2  
     , Fieldx_3
     , ( select count(*)
           from Table2
          where T1PK = Table1.T1PK )
              as [Count]
  from Table1
order
    by something

r937.com | rudy.ca

 

[waiterm]

Hi,

Many thanks for your reply, have tried that, it's certainly progress, however it gives the total number of records for the entire second table as opposed to the total number of records associated with each record in table 1.

Regards



Rob Waite

http://www.hostpipe.co.uk?src=TT

 

[waiterm]

Hiya,

Have sussed it, "where T1PK" should have been the corresponding key in table2. So all working now.

Many thanks again.



Rob Waite

http://www.hostpipe.co.uk?src=TT