Re: Join tables?

From: Dennis Hart <dennis_at_csadfa.cs.adfa.oz.au>
Date: Wed, 12 May 1993 23:34:09 GMT
Message-ID: <1993May12.233409.10025_at_sserve.cc.adfa.oz.au>


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).
>
Try select DISTINCT company, address from ...etc...

> 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.
>
As an aside, having multiple NAME fields like you apparently do here is not generally a good idea - read any good database textbook on the principles of data normalization (e.g. C.J. Date: "Introduction to Database Systems" 5th ed.

Hope this helps,
Cheers,
Dennis Hart

> Thanks for any help.
>
> --
> Nick Maximov
> <maxs_at_adnserv.kaija.spb.su>
Received on Thu May 13 1993 - 01:34:09 CEST

Original text of this message