Thread: Re: Selecting from two tables

> -----Original Message-----
> From: pgsql-php-owner@postgresql.org [mailto:pgsql-php-
> owner@postgresql.org] On Behalf Of Michael Hanna
> Sent: 14 January 2004 23:33
> To: pgsql-php@postgresql.org
> Subject: [PHP] Selecting from two tables
>
>   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?
>
> applications has a jobid foreign key
>
> create table job (      jobid SERIAL,
>                          login varchar(40),
>                          jobtitle varchar(70),
>                          jobdescrip TEXT,
>                          jobkeywords TEXT,
>                          valid int2 NOT NULL,
>                          postdate TIMESTAMPTZ,
>                          PRIMARY KEY (jobid),
>                          FOREIGN KEY(login) REFERENCES company
>                              ON DELETE CASCADE
>                          );
>
>
> CREATE TABLE application        (   applid SERIAL,
>                                      jobid INTEGER,
>                                      login varchar(6),
>                                      apldate TIMESTAMPTZ,
>                                      PRIMARY KEY (applid),
>                                      FOREIGN KEY (jobid) REFERENCES
job
>                                          ON DELETE CASCADE,
>                                      FOREIGN KEY (login) REFERENCES
> student
>                                          ON DELETE CASCADE
>                                  );
>
>

How about using a sub-select.

SELECT t1.*, (SELECT COUNT(*) FROM application s1 WHERE
s1.jobid=t1.jobid)  AS app_count FROM job t1;


Nick

>> 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