Skip site navigation (1) Skip section navigation (2)

Re: Selecting from two tables

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 (view raw or flat)
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




pgsql-php by date

Next:From: Seader, CameronDate: 2004-01-15 22:41:03
Subject: timestamptz Insert
Previous:From: mauro.folcarelliDate: 2004-01-15 07:44:09
Subject: Re: Selecting from two tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group