Re: Join tables?

In article <1993May08.074008.1173_at_kaija.spb.su>, maxs_at_adnserv.kaija.spb.su (Nick Maximov) writes:
> I have 3 tables: Companies, Addresses and Productions. I made a view
> where these tables is joined by keyfield 'Key':
> CREATE VIEW TOTAL (COMPANY, ADDRESS, PRODUCTION)
> AS SELECT C.COMPANY_NAME, A.ADDRESS, P.PRODUCTION
> FROM COMPANIES C, ADDRESSES A, PRODUCTIONS P
> WHERE C.KEY = A.KEY AND C.KEY = P.KEY;
>
> But when I try to get just Company Name and Address with
> SELECT COMPANY, ADDERSS FROM TOTAL WHERE COMPANY = 'IBM'
> I get more then one row because the KEY field is multiply
> defined in PRODUCTIONS table (that company is develops a lot of goods).
>
> How to create view to avoid duplicates? GROP BY expression does not work
> because I really have fields as NAME1||NAME2 and F1*8/2 and so on.
>

CREATE VIEW TOTAL (COMPANY, ADDRESS, PRODUCTION) AS SELECT C.COMPANY_NAME, A.ADDRESS, P.PRODUCTION   FROM COMPANIES C, ADDRESSES A, PRODUCTIONS P  WHERE C.KEY = A.KEY AND C.KEY = P.KEY
   AND P.rowid IN (SELECT MAX(rowid)

                     FROM productions P2
                    WHERE P2.key=P.key);

kind regards

Mike

                                                                          ^
 Federal Ministry of Science and Research                               B | M
<-------------------------------------------------------------------------+---->
 Computer Center                                                        W | F
                                                                          |
 Ing. Klaus-Michael Hatzinger       mail: hatzinger_m_at_mail.bmwf.gv.at     |
 Bankgasse 1/209                   phone: 0043-222-53120/5188             |
 1014 Vienna, Austria                fax: 0043-222-53120/5180             V
================================================================================
======      ORACLE-Application Engineer and Database Administrator        ======
======      Using Oracle V6.0.34 on DEC/VMS, V6.0.36 on RISC/ULTRIX       ======
================================================================================