pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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






view thread (3+ messages)

Message-ID: <8F4A22E017460A458DB7BBAB65CA6AE502AA46@openmanage>
Permalink:  ../8F4A22E017460A458DB7BBAB65CA6AE502AA46@openmanage/
Also on:    postgresql.org/message-id/8F4A22E017460A458DB7BBAB65CA6AE502AA46@openmanage

 · 

reply

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