Fog Creek Software
Discussion Board

Tricky SQL


If I have two tables in a one to many relationship, can I write a SQL statement the gets all the info from the first table, along with the number of related entries in the second table?

Like so:

ID      animal
1        cow
2        horse

animalID        name
1                    Bob
1                    Mary
2                    Linda

From this, I want a resultset like:

Animal    Count
Cow          2
Horse        1

Please help : )

Thursday, July 31, 2003

"SELECT a.animal, COUNT(*) FROM tblAnimal as a, tblIndividual as i WHERE a.ID=i.animalID GROUP BY a.animal"?

Thursday, July 31, 2003


Off the top of my head, using ANSI-92 syntax:

select  ANI.Animal, count(*)
from    tblAnimal ANI
    INNER JOIN tblIndividual IND ON IND.animalID = ANI.ID
group by ANI.Animal

Author of "Comprehensive VB .NET Debugging"

Mark Pearce
Thursday, July 31, 2003

Any other school homework assignment that you'd like  getting done ? <g>

anonymous troll
Thursday, July 31, 2003

Hee. yes this is tricky because most SQL implementations will complain if you try to return any field that is neither a group expression (COUNT, MAX) nor in the GROUP BY clause. (I think this is the way the ANSI specifies behaviour)

The only way I have been able to get this to work in Oracle is like this:

SELECT a.*, b.cnt
FROM tblAnimal a, (SELECT animalId, count(*) as cnt from tblIndividual GROUP BY animalID) b
WHERE a.ID = b.animalID

I don't know what syntax will work with MS SQL-Server.

Thursday, July 31, 2003

Inner Join will only return values in one table that have associated values in the second table.  To demonstrate, add the value "Mule" to the tblAnimal table and run the code above again.  "Mule" will only show up in the result set if it has a name in the tblIndividual table.


    count(i.animalid) as animal_count
    tblanimal a left outer join
          tblindividual i on i.animalid =
group by


Jeff MacDonald
Thursday, July 31, 2003

The above will parse in SQL Server just fine.  When I say, "the code above" in my previous message, I mean the code using Inner Join at the top of the thread, not the sub-Select statement recently posted.

Jeff MacDonald
Thursday, July 31, 2003

Jeff, will

select  ANI.ID, ANI.Animal, count(*)
from    tblAnimal ANI
    INNER JOIN tblIndividual IND ON IND.animalID = ANI.ID
group by ANI.Animal

work in SQL-Server?

Thursday, July 31, 2003

In your example above, you'd have to include "" in the Group By clause for it to parse in SQL Server (I think this is a SQL-92 thing, not specific to SQL Server).

By adding "" to the Group By, it will parse in SQL just fine.  However, any animals in the tblanimals table that do not have a name associated with them will not show up in the result set (see above comment on join types).

So I suppose you're cutting your teeth on the new wave of BRM software (Barnyard Resource Management) - don't forget us little guys when you hit the big time!!    :)

Jeff MacDonald
Friday, August 1, 2003

Hey guys! Thanks alot!
and no, its not homework, I just couldnt figure out how to do it. Its hard to google for things like this. :-)

Friday, August 1, 2003

Try google groups. They provide more technical answers than traditional web sites. They're resource oriented versus financially oriented, being posted by users instead of businesses. I find that difficult, specific questions have answers that reside in the newsgroups, even when they are absent from the web.

Dustin Alexander
Friday, August 1, 2003 is much better than googling for sql questions. 

Jason Watts
Friday, August 1, 2003

*  Recent Topics

*  Fog Creek Home