Selecting from two tables

From: Michael Hanna <taojones(at)sympatico(dot)ca>
To: pgsql-php(at)postgresql(dot)org
Subject: Selecting from two tables
Date: 2004-01-14 23:33:13
Message-ID: 05A7AFE7-46EA-11D8-A66C-000A95B34330@sympatico.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice pgsql-php

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
);

thanks for any help on this..
Michael

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message glenn 2004-01-15 00:48:09 Re: probs with 7.3 -> 7.4 on debian
Previous Message Tom Lane 2004-01-14 19:24:16 Re: parser-error

Browse pgsql-php by date

  From Date Subject
Next Message mauro.folcarelli 2004-01-15 07:44:09 Re: Selecting from two tables
Previous Message mauro.folcarelli 2004-01-14 14:42:32 pg_connect(): Cannot create new link