Re: [SQL] 2 Table Select

Andy Lewis wrote: >I am trying to get the _total_ number of jobs >that each company has in the db. > >In other words I
wantto get each distinct company >that has jobs in the jobs table and the _total_ number  >of jobs that it has in the
jobstable. > >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
ID32B8FAA1                ========================================    "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