Ms SQL Server basic tutorial-operate on multiple table stickers)

So far, you have only tried to use an SQL statement to retrieve data from a table. You can also use a SELECT statement to retrieve data from multiple tables at the same time. You only need to list the names of the tables from which data is to be retrieved in the from clause of the SELECT statement:

Select au_lname, title from authors, titles

When this select statement is executed, data is retrieved from both the table authors and table titles. Extract all the author names from the table authors and all the names of the authors from the table titles. Run this query in the iSQL/W program and check the query result. You may find some strange unexpected situations: the author's name does not match their book, but all possible combinations of the author's name and title appear, this may not be what you want to see.

What went wrong? The problem is that you did not specify the relationship between the two tables. You didn't tell SQL in any way how to associate a table with a table. Because you do not know how to associate two tables, the server can only simply return all possible combinations of records from the two tables.

To select a meaningful record combination from the two tables, you need to establish the relationship between the fields in the two tables to associate the two tables. One way to do this is to create a third table to describe the relationship between fields in the other two tables.

Table authors has a field named au_id, which contains the unique identifier of each author. Table titles has a field named title_id that contains the unique identifier of each title. If you can establish a relationship between the au_id field and the title_id field, you can associate the two tables. The database pubs has a table named titleauthor, which is used to complete this task. Each record in a table contains two fields used to associate table titles with table authors. The following SELECT statement uses the three tables to obtain the correct results:

Select au_name, title from authors, titles, titleauthor

Where authors. au_id = titleauthor. au_id

And titles. title_id = titleauthor. title_id

When this select statement is executed, each author will match the correct title. Table titleauthor specifies the relationship between table authors and table titles, which is achieved by including each field from the two tables. The unique purpose of the third table is to establish a relationship between the fields of the other two tables. It does not contain any additional data.

Note how the field name is written in this example. To distinguish the same field name au_id in Table authors and table titles, a table name prefix and a full stop are added before each field name. The field named author. au_id belongs to the table authors and the field named titleauthor. au_id belongs to the table titleauthor.

By using the third table, you can establish various types of relationships between two table fields. For example, an author may write many different books or a book may be completed by many different authors. When the fields in two tables have this "many-to-many" relationship, you need to use the third table to specify this relationship.

However, in many cases, the relationship between two tables is not complex. For example, you need to specify the relationship between table titles and table publishers. Because a title cannot match with multiple publishers, you do not need to specify the relationship between the two tables through the third table. To specify the relationship between the table titles and the table publishers, you only need to make the two tables have a public field. In the database pubs, the table titles and the table publishers both have a field named pub_id. If you want to get a list of titles and Their publishers, you can use the following statement:

Select title, pub_name from titles, publishers

Where titles. pub_id = publishers. pub_id

Of course, if a book is jointly published by two publishers, you need a third table to represent this relationship.

Generally, when you first know that there is a "many-to-many" relationship between the fields in the two tables, use the third table to join the two tables. If the fields in two tables have only one-to-one or one-to-many relationships, you can use public fields to associate them.