Application of select query (II.)

Use of the SELECT Join clause
Join is a very good use of a SELECT query clause, although you have it after the query has become very long, the probability of error has increased; But after one-fourth pillars of incense, you will fall in love with it, because I decided to give it all the advantages of showing you!
To make it easier for readers to accept, let's build a database first.

#货物表
CREATE TABLE Goods (
ID int auto_increment,
The code varchar (TEN) is not NULL,
Name varchar (20),
Spec varchar (40),
Grade varchar (10),
Primary key (ID));

#业务员表
CREATE TABLE Staff (
ID int auto_increment,
Name varchar (20),
Depart int NOT NULL,
Primary key (ID));

#部门表
CREATE TABLE Depart (
ID int auto_increment,
Name varchar (20),
Primary key (ID));

#客户表
CREATE TABLE Customer (
ID int auto_increment,
Name varchar NOT NULL,
Association varchar (20),
Tel varchar (30),
Fax varchar (30),
Addr varchar (80),
Postcode varchar (10),
Email varchar (40),
Primary key (ID));

#销售记录表
CREATE TABLE Sales (
ID int auto_increment,
Inputime datetime NOT NULL,
Staff int NOT NULL,
Customer int NOT NULL,
Good_code varchar (TEN) is not NULL,
Amount Decimal (10,2) NOT NULL default 0.00,
Price Decimal (8,2) is not NULL default 0.00,
Memo text,
Primary key (ID));

#插入数据
INSERT into goods (Code,name,spec,grade)
VALUES (' A0001 ', ' Display ', ' PHILIPS 105B ', ' excellent ');
INSERT into goods (Code,name,spec,grade)
VALUES (' A0002 ', ' Display ', ' PHILIPS 107B ', ' excellent ');
Insert into depart (name)
VALUES (' Business One ');
Insert into depart (name)
VALUES (' Business two department ');
Insert into depart (name)
VALUES (' Business three department ');
INSERT into staff (Name,depart)
Values (' King old Five ', 2);
INSERT into staff (Name,depart)
VALUES (' John ', 3);
INSERT into staff (Name,depart)
VALUES (' Dick ', 1);
INSERT into staff (Name,depart)
VALUES (' Zhao ', 3);
Insert into customer (Name,association,tel,fax,email)
VALUES (' Ding fat Computer Company ', ' ding fat ', ' 12345678 ', ' 12345679 ', ' fatding@fat.org ');
Insert into sales (Inputime,staff,customer,good_code,amount,price)
Values (now (), 2,1, ' A0001 ', 10,1200.00);
Insert into sales (Inputime,staff,customer,good_code,amount,price)
Values (now (), 6,2, ' A0001 ', 10,1200.00);

I must not have a field to explain, in which the field names are very common AH. We do this in order to not directly record the name of the goods, the specifications, the name of the customer, the name of the salesman, and so on in the huge sales record list--that's too wasteful. We put all the possible goods, salesman, customer and so on as a table, they have a unique identification in their table number, and in the sales record sheet, just fill in this number.
When viewing the sales record, convert the goods code into its corresponding goods name and specification, grade, etc., and convert the customer's number to the customer's name, and the clerk's number to his name. Let's use the join clause and look at the following query:

SELECT Sales.id,sales.inputime,sales.amount,sales.price,sales.memo,
Staff.name as staff,depart.name as depart,customer.name as customer,
Goods.name as good_name,goods.spec as good_spec,goods.grade as Good_grade
From the sales INNER JOIN staff on Staff.id=sales.staff
INNER JOIN depart on Depart.id=staff.depart
INNER JOIN Customer on Customer.id=sales.customer
INNER JOIN goods on Goods.code=sales.good_code
ORDER BY inputime Desc

Note that this is not a few, is a SELECT statement!! Well, it's long. Because the result of the query is also relatively long, write out everyone may not be able to see clearly, so please try it yourself. The results of the query, the respective fields correspond to:

Inputime Time of entry
Amount Sales Quantity
Price prices
Memo Notes
Staff salesman name
Depart Sales Department
Customer Name
Good_name Cargo Name
GOOD_SPEC Cargo Specification
Good_grade Cargo Grade

When the value of the Staff field in the sales table, in the staff table can not find the corresponding salesman record, this may be caused by two reasons: a mistake to delete the salesman, two for this sales record when filling in the sale table error occurred. In this case, you cannot take this record out using one of the above queries. Just now I built the database sales table deliberately left a salesman ID is 6 records-the clerk table no ID is 6! So according to the above query, there is no query to this record. If you want to avoid this, you can use "left join": regardless of whether it is matched or not, remove all the records from the table on the left, and all records in the right table that cannot be matched are null. The above enquiry should read:

SELECT Sales.id,sales.inputime,sales.amount,sales.price,sales.memo,
Staff.name as staff,depart.name as depart,customer.name as customer,
Goods.name as good_name,goods.spec as good_spec,goods.grade as Good_grade
From the sales left JOIN staff on Staff.id=sales.staff
Left JOIN depart on Depart.id=staff.depart
Left JOIN customer on Customer.id=sales.customer
Left JOIN goods on Goods.code=sales.good_code
ORDER BY inputime Desc

Then use this statement query once, is not more than just found a salesman and belong to the department is a null record? The clerk was mistakenly deleted should be absolutely prohibited, fill in the Sales table error should also be avoided. But once it happens, it should not affect the normal access to the entire sales record data. So it is necessary to use the "left connection".

Time is short, make a draft;