X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5D234D1B47A for ; Thu, 15 Jan 2004 08:38:20 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 01382-03 for ; Thu, 15 Jan 2004 04:37:50 -0400 (AST) Received: from webbased16.localdomain (unknown [213.152.63.91]) by svr1.postgresql.org (Postfix) with ESMTP id 5DBDDD1D8AF for ; Thu, 15 Jan 2004 04:37:44 -0400 (AST) Received: from webbased10 (firewall [213.152.63.90]) by webbased16.localdomain (8.11.6/8.11.6) with ESMTP id i0F8j3g00410; Thu, 15 Jan 2004 08:45:03 GMT From: "Nick Barr" To: "'Michael Hanna'" , Subject: Re: Selecting from two tables Date: Thu, 15 Jan 2004 08:37:40 -0000 Message-ID: <8F4A22E017460A458DB7BBAB65CA6AE502AA46@openmanage> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 In-Reply-To: <8F4A22E017460A458DB7BBAB65CA6AE5127620@openmanage> Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/12 X-Sequence-Number: 2528 > -----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