From: Nick Barr <nick.barr@webbased.co.uk>
To: 'Michael Hanna' <taojones@sympatico.ca>
To: pgsql-php@postgresql.org
Subject: Re: Selecting from two tables
Date: Thu, 15 Jan 2004 08:37:40 -0000
Message-ID: <8F4A22E017460A458DB7BBAB65CA6AE502AA46@openmanage> (raw)
In-Reply-To: <8F4A22E017460A458DB7BBAB65CA6AE5127620@openmanage>
> -----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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: nick.barr@webbased.co.uk, taojones@sympatico.ca
Subject: Re: Selecting from two tables
In-Reply-To: <8F4A22E017460A458DB7BBAB65CA6AE502AA46@openmanage>
References: <8F4A22E017460A458DB7BBAB65CA6AE5127620@openmanage> <8F4A22E017460A458DB7BBAB65CA6AE502AA46@openmanage>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox