SQL Many Table Relation


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: SQL Many Table Relation

  1. #1
    sa Guest

    SQL Many Table Relation


    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

  2. #2
    Arthur Wood Guest

    Re: SQL Many Table Relation


    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



  3. #3
    Phil Weber Guest

    Re: SQL Many Table Relation

    > 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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center