Re: Selecting from two tables - Mailing list pgsql-php

>> I have a table called Jobs and another called Applications.
>> I can query all Job rows, but how do I also include the number
>> of Application rows for each Job row in the SELECT statement?

Try this way:

(1) CREATE VIEW viewappl AS
        SELECT jobid
              ,count(*) as numappl
        FROM application
        GROUP BY
               jobid;

(2) SELECT
  job.jobid
 ,job.login
 ,job.jobtitle
 ,job.jobdescrip
 ,job.jobkeywords
 ,job.valid
 ,job.postdate
 ,viewappl.numappl
 FROM job , viewappl
 where job.jobid = viewappl.jobid
 order by
  job.jobid
 ;

I hope it's ok.

Hi, Mauro