Обсуждение: Trouble with JOINS

Hi!

I have some stupid problem wich is driving me nuts.

This is my 2 tables.

CREATE TABLE one ( one_id varchar(23)
   day varchar(10),
   view int4,
   two_id varchar(32),
   PRIMARY KEY (one_id)
);

CREATE TABLE two ( tow_id varchar(32),
   user varchar(12) NOT NULL,
   PRIMARY KEY (client_id)
);

table one is tracking views of some user, and table two is actual data
of that user.

What i want to get from SQL is something like this

user    view
u1      34
u2      14
u3      8

The data in tabel are like this
two_id     day    view   one_id
aaa         1      15      1
bbb         1       7      2
ccc         2       2      3
bbb         2       7      4
aaa         3      19      5
ccc         3       6      6


two_id    user
aaa       u1
bbb       u2
ccc       u3

I hope you'll understand what i want to do. If not tell me.

--
Thanks,
 Uros

Uros Gruber wrote:

>
> I hope you'll understand what i want to do. If not tell me.

    It would be so much nicer to have significative table and column names,
as well as explicit referential integrity...



--
  _
/ \   Leandro Guimarães Faria Corsetti Dutra         +55 (11) 3040 8913
\ /   Amdocs at Bell Canada                          +1 (514) 786 87 47
  X    Support Center, São Paulo, Brazil          mailto:adbaamd@bell.ca
/ \   http://terravista.pt./Enseada/1989/    mailto:leandrod@amdocs.com

Uros Gruber wrote:
  >Hi!
  >
  >I have some stupid problem wich is driving me nuts.
  >
  >This is my 2 tables.
  >
  >CREATE TABLE one ( one_id varchar(23)
  >   day varchar(10),
  >   view int4,
  >   two_id varchar(32),
  >   PRIMARY KEY (one_id)
  >);
  >
  >CREATE TABLE two ( tow_id varchar(32),
  >   user varchar(12) NOT NULL,
  >   PRIMARY KEY (client_id)
  >);
  >
  >table one is tracking views of some user, and table two is actual data
  >of that user.
  >
  >What i want to get from SQL is something like this
  >
  >user    view
  >u1      34
  >u2      14
  >u3      8
  >
  >The data in tabel are like this
  >two_id     day    view   one_id
  >aaa         1      15      1
  >bbb         1       7      2
  >ccc         2       2      3
  >bbb         2       7      4
  >aaa         3      19      5
  >ccc         3       6      6
  >
  >
  >two_id    user
  >aaa       u1
  >bbb       u2
  >ccc       u3
  >
  >I hope you'll understand what i want to do. If not tell me.


SELECT user, sum(view)
  FROM one, two
  WHERE one.two_id = two.two_id
  GROUP BY user;

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "My dear brothers, take note of this: Everyone should
      be quick to listen, slow to speak and slow to become
      angry, for man's anger does not bring about the
      righteous life that God desires."  James 1:19,20