From: | "Nick Barr" <nick(dot)barr(at)webbased(dot)co(dot)uk> |
---|---|
To: | "'Michael Hanna'" <taojones(at)sympatico(dot)ca>, <pgsql-php(at)postgresql(dot)org> |
Subject: | Re: Selecting from two tables |
Date: | 2004-01-15 08:37:40 |
Message-ID: | 8F4A22E017460A458DB7BBAB65CA6AE502AA46@openmanage |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-php |
> -----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
From | Date | Subject | |
---|---|---|---|
Next Message | Seader, Cameron | 2004-01-15 22:41:03 | timestamptz Insert |
Previous Message | mauro.folcarelli | 2004-01-15 07:44:09 | Re: Selecting from two tables |