> -----Original Message----- > From: pgsql-php-owner@xxxxxxxxxxxxxx [mailto:pgsql-php- > owner@xxxxxxxxxxxxxx] On Behalf Of Michael Hanna > Sent: 14 January 2004 23:33 > To: pgsql-php@xxxxxxxxxxxxxx > 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
