Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    3

    Unanswered: Counting Rows that refence another table

    Hi,

    I have two tables,

    Table A(A_ID, Info)

    Table B(B_ID, A_ID, Blah) where B.A_ID references A.A_ID

    How can I detemine how many records in table B reference each unique A_ID in table A?

    I've tried the following but it doesn't work:

    Code:
    Select A.A_ID, COUNT(B.A_ID) FROM A
    JOIN B ON A.A_ID = B.A_ID

  2. #2
    Join Date
    Oct 2005
    Posts
    3
    I've figured out the counting using this

    Code:
    Select A.A_ID, COUNT(B.A_ID) FROM A
    JOIN B ON A.A_ID = B.A_ID
    GROUP BY A.A_ID
    Just needed to add the 'group by'

    Can anyone tell me how I can restrict this to only show cases where there are (for example) 3 or more records in table B that reference A_ID?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTableA(A_Id int)
    CREATE TABLE myTableB(B_Id int, A_Id int)
    GO
    
    INSERT INTO myTableA(A_Id) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    INSERT INTO myTableB(B_Id, A_Id) 
    SELECT 1,1 UNION ALL SELECT 1,2 UNION ALL SELECT 1,3 UNION ALL
    SELECT 2,1 UNION ALL SELECT 2,2 UNION ALL
    SELECT 3,1
    GO
    
    SELECT a.A_Id, COUNT(*)
    FROM myTableA a INNER JOIN myTableB b ON a.A_Id = b.B_Id
    GROUP BY a.A_Id
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTableA, myTableB
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Having Count(*) > 2
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •