SQL REQ: counting data and multiple tables

From: Alex Skolnic <ak_at_e-brag.com>
Date: Wed, 28 Mar 2001 19:06:01 GMT
Message-ID: <jvc4ct8gfvrf20fusmp0sf7ipohtnqi9ff@4ax.com>

HI All,

What I would to accomplish is a way that I can query my accounts table and then use the emp_id to determine how many enteries that emp_id has in another table. I am currently using multiple SQL statements:

For the Accounts table:
Select emp_id, Name from Accounts

To find the number of instances
select count(emp_id) from purchase where empid=(emp_id to look for);

is it possible to do this type of query with ONE SQL statement ( Possibly a view/Stored Proc) and have it return:

Emp_id          Name          Count (purchase table)
11111           abc               20
22222           def                1
33333           ghi                20

Is this even possible? If not, what would be the best way to accomplish this with a single select statement?

Any and all help would be greatly appreciated. Received on Wed Mar 28 2001 - 13:06:01 CST