Application of select query (II.)

Source: Internet
Author: User
Tags insert join
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.

ID int auto_increment,
The code varchar (TEN) is not NULL,
Name varchar (20),
Spec varchar (40),
Grade varchar (10),
Primary key (ID));

ID int auto_increment,
Name varchar (20),
Depart int NOT NULL,
Primary key (ID));

ID int auto_increment,
Name varchar (20),
Primary key (ID));

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));

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 ', ' ');
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.inputime,sales.amount,sales.price,sales.memo, as staff, as depart, as customer, as good_name,goods.spec as good_spec,goods.grade as Good_grade
From the sales INNER JOIN staff on
INNER JOIN depart on
INNER JOIN Customer on
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.inputime,sales.amount,sales.price,sales.memo, as staff, as depart, as customer, as good_name,goods.spec as good_spec,goods.grade as Good_grade
From the sales left JOIN staff on
Left JOIN depart on
Left JOIN customer on
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;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.