Skip site navigation (1) Skip section navigation (2)

Subquery and where clause ?

From: Bo Lorentsen <bl(at)netgroup(dot)dk>
To: pgsql-novice(at)postgresql(dot)org
Subject: Subquery and where clause ?
Date: 2001-08-22 12:18:53
Message-ID: 3B83A32D.E75698B5@netgroup.dk (view raw or flat)
Thread:
Lists: pgsql-novice
Hi ...

I have been trying to find a way to make several differant table
counters on one request, and therefor tried to use sub queries as my
solution, but I can't make it work and hope someone could help me out
:-(

Lets say I have 3 tables A, B and C and both A and B have a references
to C. I like to make a view that for each C counts the number of
refering A's and B's.

Tables :

CREATE TABLE A (
    INTEGER id,
    INTEGER ref );

CREATE TABLE B (
    INTEGER id,
    INTEGER ref );

CREATE TABLE C (
    INTEGER id );

As I can see it, the SQL would look somthing like this :

    SELECT C.id, A.count, B.count FROM C,
        (SELECT COUNT( A.id ) FROM A WHERE A.ref = C.id) AS A,
        (SELECT COUNT( B.id ) FROM B WHERE B.ref = C.id) AS B;

But the result is the same count for each row :-(

This is off cause a very simple example.

What have I done wrong ?

/BL


Responses

pgsql-novice by date

Next:From: Bo LorentsenDate: 2001-08-22 12:34:58
Subject: Re: Subquery and where clause ?
Previous:From: Francois ThomasDate: 2001-08-22 08:16:28
Subject: Bad timestamp external representation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group