Re: Selecting from two tables

> -----Original Message-----
> From: pgsql-php-owner(at)postgresql(dot)org [mailto:pgsql-php-
> owner(at)postgresql(dot)org] On Behalf Of Michael Hanna
> Sent: 14 January 2004 23:33
> To: pgsql-php(at)postgresql(dot)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