Re: Selecting from two tables

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




---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match