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.Elphick@lfix.co.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 
> 
>