Thread: Help with JOINING 3 tables

Hi!

I have this 3 tables:

CREATE TABLE clients (
        client_id CHAR(32) NOT NULL,
        created TIMESTAMP DEFAULT 'now' NOT NULL,
        modified TIMESTAMP DEFAULT 'now' NOT NULL,
        username VARCHAR(12) NOT NULL,
        .
        .
        PRIMARY KEY (client_id)
);

client_id   username
1            u1
2            u2
3            u3

CREATE TABLE statistics (
        created TIMESTAMP,
        host INET NOT NULL,
        stat_type CHAR(1), --possible value (v,c)
        banner_id INTEGER NOT NULL,
);

stat_type     banner_id
v               1
v               1
v               5
c               5
v               3
v               4
c               4
v               6
c               6
v               2
v               2
c               2

CREATE TABLE banners (
        banner_id SERIAL NOT NULL,
        created TIMESTAMP DEFAULT 'now' NOT NULL,
        modified TIMESTAMP DEFAULT 'now' NOT NULL,
        banner TEXT NOT NULL,
        .
        .
        client_id VARCHAR(32) NOT NULL,
        PRIMARY KEY (banner_id)
);

banner_id      banner     client_id
1               b1          1
2               b2          2
3               b3          1
4               b4          3
5               b5          1
6               b6          3

I want to count how many v or c are in table statistics for some
client.

something like this:

client_id      views     clicks
1                4         1
2                2         1
3                2         2


Is is possible to write some function or is it possible to do this
with some complex join or smth. I'm trying this for hours... no
luck:((
please help!


Thanks,
--
Uroš

On Mon, 8 Jan 2001, Uro Gruber wrote:

> I want to count how many v or c are in table statistics for some
> client.
>
> something like this:
>
> client_id      views     clicks
> 1                4         1
> 2                2         1
> 3                2         2

Try something like this:

SELECT b1.client_id, (SELECT count(s.stat_type)
                      FROM statistics s, banners b2
                      WHERE b2.client_id = b1.client_id
                      AND s.banner_id = b2.banner_id
                      AND s.stat_type = 'v') AS views,
                     (SELECT count(s.stat_type)
                      FROM statistics s, banners b2
                      WHERE b2.client_id = b1.client_id
                      AND s.banner_id = b2.banner_id
                      AND s.stat_type = 'c') AS clicks
FROM banners b1
GROUP by b1.client_id
ORDER by b1.client_id;

You can simplyfy this by creating a function to count the stats like this:

CREATE FUNCTION count_stats(text, text) RETURNS integer AS '
    SELECT count(s.stat_type)
        FROM statistics s, banners b
        WHERE b.client_id = $1
        AND s.banner_id = b.banner_id
        AND s.stat_type = $2
' LANGUAGE 'SQL';

Then the query becomes:

SELECT client_id, count_stats(client_id, 'v') as views,
                  count_stats(client_id, 'c') as clicks
FROM banners
GROUP by client_id
ORDER by client_id;
--
Tod McQuillin