Re: [SQL] 2 Table Select

Thanks, I have it now....

Andy

On Thu, 27 May 1999, Oliver Elphick wrote:

> Andy Lewis wrote:
> >I am trying to get the _total_ number of jobs
> >that each company has in the db.
> >
> >In other words I want to get each distinct company
> >that has jobs in the jobs table and the _total_ number
> >of jobs that it has in the jobs table.
> >
> >What would be the best query that I could use?
> >
> >I'm using 6.4.2.
> >
> >Table = jobs
> >+-------------+---------------+-------+
> >| Field | Type | Length|
> >+-------------+---------------+-------+
> >| job_id | int4 not null | 4 |
> >| company_id | int4 | 4 |
> >| title | varchar() | 50 |
> >...
> >+-------------+---------------+-------+
> >
> >Table = company
> >+--------------+---------------+-------+
> >|Field |Type | Length|
> >+--------------+---------------+-------+
> >| company_id | int4 not null | 4 |
> >| company_name | varchar() | 100 |
> >| email | varchar() | 100 |
> >...
> >+--------------+---------------+-------+
>
> select c.company_id, count(j.job_id)
> from company as c, jobs as j
> where c.company_id = j.company_id
> group by c.company_id;
>
> --
> Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
> Isle of Wight http://www.lfix.co.uk/oliver
> PGP key from public servers; key ID 32B8FAA1
> ========================================
> "All that the Father giveth me shall come to me; and
> him that cometh to me I will in no wise cast out."
> John 6:37
>
>