Re: Selecting from two tables
Re: Selecting from two tables
От:
"Nick Barr" <nick.barr@webbased.co.uk>
Дата:
> -----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
Re: Selecting from two tables
От:
mauro.folcarelli@sistinf.it
Дата:
>> 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?
Try this way:
(1) CREATE VIEW viewappl AS
SELECT jobid
,count(*) as numappl
FROM application
GROUP BY
jobid;
(2) SELECT
job.jobid
,job.login
,job.jobtitle
,job.jobdescrip
,job.jobkeywords
,job.valid
,job.postdate
,viewappl.numappl
FROM job , viewappl
where job.jobid = viewappl.jobid
order by
job.jobid
;
I hope it's ok.
Hi, Mauro