SQL Many Table Relation

[sa]

Hi Everyone
How can you write SQL Statement Relation Between tables
many to many
one to one
many to one
one to many
For example I have got 5 Tables
3 out of 5 has got field called "CustID" ,"CustName" ........
2 out of 5 has got field called "CustName" ......
What kind of SQL statement will select all the "CustID" ,"CustName" from
5 tables
without duplicating
Many Many Thanks
salih

[Arthur Wood]

SA,
Many-to-Many : example:

TblAuthors and TblBooks:

Any One Author may in fact have written MANY individual BOOKS
Any One Book may in fact have MORE THAN 1 Author
hence MANY-To-MANY relationship between Authors and Books, requiring
an intermediate BooksAuthors Table such that the relation between Books and
BooksAuthors is One-to-Many (Any One Book may have Many records in the BooksAuthors
Table, but any one record in the BooksAuthors Table has a UNIQUE Author),
similarly for the relation between Authors and BooksAuthors.

One-To-One (not very common, but can occur)
One Record in the First Table has EXACTLY one record in the Second
table, and vice-versa)

Usually reserved for cases where the records in the second table are
proprietary (for instance, an EMPLOYEE TABLE which holds general Employye
data, and a CURRENTSALARY table which holds THAT employees Current Salary
information, which the company DOES NOT want to be available to ANYONE who
has access to the general Employee record - admittedly this is a bit of a
contrived example, but you get the idea)

One-to-Many - Quite Common (see the discussion of the many-to-many case
above) In this relationship, each record in the ONE table may have 0 OR
MORE records in the MANY table, but each record in the MANY table has EXACTLT
1 recoird in the ONE table .

It is rarely a good idea to have "CustName" as a value in ANY table other
than the MAIN Customer table, and you would then have the CUSTID in each
of the related tables. This a basic principle of Database Table desing,
which says that each pice of data should only be stored in ONE place (not
that CustID IS NOT considered a piece of data in this sense, but CustName
IS a pice of data). The reason being that if the Name of the Customer should
change (women have been known to get married, hence chaing their name - At
least their LAST name). With your structure, that would require changing
the CustName value in at least 2 tables, and that opens up the possibility
that 1) you forget to make the change in one of the two tables, or even worse,
you DO NOT MAKE THE SAME change in BOTH Tables. Bit if the CustName is ONLY
in the Customer Table, then when you change it THERE, and then draw that
field from the CustomerTable when using the other tables, the SAME CustName
is used EVERYWHERE.

"Select Table1.CustID, CustomerTable.CustName, Table1.Value from table1 INNER
JOIN CustomerTable on Table1.CustID = CustomerTable.CustID" or

"Select Table1.CustID, CustomerTable.CustName, Table1.Value from table1 ,
CustomerTable where Table1.CustID = CustomerTable.CustID"

Arthur Wood


By the way, this question has NOTHING WHATSOEVER to do with .NET, but has
EVERYTHING to do with basic Database Design Principles. If you do NOT understand
the concepts of MANY-To-MANY, ONE-to-MANY, etc, then I strongly suggest that
you need to learn about the basics of Relational Database design BEFORE you
go too much farther.

Arthur Wood

"sa" <salih.ayan@UKAutomation> wrote:
>
>Hi Everyone
>How can you write SQL Statement Relation Between tables
>many to many
>one to one
>many to one
>one to many
>For example I have got 5 Tables
>3 out of 5 has got field called "CustID" ,"CustName" ........
>2 out of 5 has got field called "CustName" ......
>What kind of SQL statement will select all the "CustID" ,"CustName" from
>5 tables
>without duplicating
>Many Many Thanks
>salih

[Phil Weber]

> By the way, this question has NOTHING WHATSOEVER to
> do with .NET, but has EVERYTHING to do with basic Database
> Design Principles. If you do NOT understand the concepts of
> MANY-To-MANY, ONE-to-MANY, etc, then I strongly suggest
> that you need to learn about the basics of Relational Database
> design BEFORE you go too much farther.

....or at least post your questions to a more appropriate discussion group.
I'd suggest enterprise.sql or vb.database.design. Oops! I see that you did
post your question to vb.database.design, in addition to this one. Please do
not post the same question to multiple groups. For more information DevX
discussion group rules, see http://news.devx.com/newspolicy.asp . Thank you!
---
Phil Weber