MySQL Multi-table federated query SQL statement

The simplest kind of joint query

The code is as follows	Copy Code
SELECT * FROM table1 N, table2 i WHERE n.itemid = I.itemid

Cases

Promotion Site Table promotion_addr field: Name,id

Telephone report Unicom_record field: Date, sheet_id (associated sheet table ID)

Declarations table Sheet field: id,promotion_addr_id (associated promotion_addr table ID)

To ask for statistics on the number of promotional sites that have been completed in a particular month;

The code is as follows	Copy Code
SELECT P.name, COUNT (*) From Unicom_record as Ur Left Join sheet as s on ur.sheet_id = S.id Left Join promotion_addr as P on s.promotion_addr_id = P.id WHERE Ur.status =? ' COMPLETE ' and Ur. ' Date ' >=? ' 2010-03-01′and Ur. ' Date ' ' 2010-04-01′ GROUP by P.id

(or use the between and statement)

INNER JOIN INNER JOIN

Join_table:table_reference [INNER | CROSS] JOIN table_factor [join_condition]

external connections in MySQL

Select ID, name, action from user as U left join user_action A on u.id = a.user_id

Let's Create a table first.

CREATE TABLE EMP (
ID int NOT NULL PRIMARY key,
Name varchar (10)
);

CREATE TABLE Emp_dept (
dept_id varchar (4) NOT NULL,
emp_id int NOT NULL,
Emp_name varchar (10),
Primary KEY (dept_id,emp_id));

INSERT into EMP () values
(1, "Dennis-1″),
(2, "Dennis-2″),
(3, "Dennis-3″),
(4, "Dennis-4″),
(5, "Dennis-5″),
(6, "Dennis-6″),
(7, "Dennis-7″),
(8, "Dennis-8″),
(9, "Dennis-9″),
(Ten, "Dennis-10″");

Insert into emp_dept () values
("R&d", 1, "Dennis-1″"),
("DEv", 2, "Dennis-2″"),
("R&d", 3, "Dennis-3″"),
("Test", 4, "Dennis-4″"),
("Test", 5, "Dennis-5″");

>> LEFT Join
————-

The code is as follows	Copy Code
Select a.id,a.name,b.dept_id From EMP a LEFT join Emp_dept B on (a.id=b.emp_id);

# pick out all the data in the table EMP on the left, even if the data is not in the emp_dept, and nothing is displayed with NULL.
# The display data is based on the data in the table EMP on the left

code is as follows	copy code
mysql> Select a.id,a.name,b.dept_id -> from emp a LEFT join Emp_dept B on (a.id=b.emp_id);

+--+ ——— –+ ——— +
| id | name      | dept_id |
+--+ ——— –+ ——— +
|  1 | dennis-1  | r&d     |
|  2 | dennis-2  | dev     |
|  3 | dennis-3  | r&d     |
|  4 | dennis-4  | test    |
|  5 | dennis-5  | test    |
|  6 | dennis-6  | null    |
|  7 | dennis-7  | null    |
|  8 | dennis-8  | null    |
|  9 | dennis-9  | null    |
| 10 | Dennis-10 | null    |
+--+ ——— –+ ——— +
# pick out the person data in the table EMP that is not in table emp_dept

code is as follows	copy code
Select a.ID , a.name,b.dept_id from EMP A-left join Emp_dept B in (a.id=b.emp_id) where b.dept_id is NULL; Mysql> Select a.id,a.name,b.dept_id -> from emp a LEFT join Emp_dept B on (a.id=b.emp_id) -> where B.D EPT_ID is NULL;

+--+ ——— –+ ——— +
| ID | name | dept_id |
+--+ ——— –+ ——— +
| 6 | Dennis-6 | NULL |
| 7 | Dennis-7 | NULL |
| 8 | Dennis-8 | NULL |
| 9 | Dennis-9 | NULL |
| 10 | Dennis-10 | NULL |
+--+ ——— –+ ——— +

# Put the table emp_dept on the left (of course, based on the data in the emp_dept to display the data, the EMP is more than the emp_dept data will not be shown):

code is as follows	copy code
select a.id, a.name,b.dept_id from Emp_dept B-left join EMP A on (a.id=b.emp_id); mysql> Select a.id,a.name,b.dept_id -& Gt From Emp_dept B-left join EMP A on (a.id=b.emp_id);

+--+ ———-+ ——— +
| ID | name | dept_id |
+--+ ———-+ ——— +
| 2 | Dennis-2 | DEv |
| 1 | Dennis-1 | R&d |
| 3 | Dennis-3 | R&d |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
+--+ ———-+ ——— +

>> Right Join
—————

The code is as follows	Copy Code
Select a.id,a.name,b.dept_id From EMP a RIGHT join emp_dept B on (a.id=b.emp_id);

# to display the data based on the data in the right table emp_dept

code is as follows	copy code
mysql> Select a.id,a.name,b.dept_id -> from emp a right join emp_dept B on (a.id=b.emp_id);

+--+ ———-+ ——— +
| ID | name | dept_id |
+--+ ———-+ ——— +
| 2 | Dennis-2 | DEv |
| 1 | Dennis-1 | R&d |
| 3 | Dennis-3 | R&d |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
+--+ ———-+ ——— +
5 rows in Set (0.00 sec)

# Let's swap the table's position and try the right join.

code is as follows	copy code
Select a.ID , a.name,b.dept_id from emp_dept b right joins EMP A on (a.id=b.emp_id); Mysql> Select a.id,a.name,b.dept_id -> from emp_dept b right Join EMP A on (a.id=b.emp_id);

+--+ ——— –+ ——— +
| ID | name | dept_id |
+--+ ——— –+ ——— +
| 1 | Dennis-1 | R&d |
| 2 | Dennis-2 | DEv |
| 3 | Dennis-3 | R&d |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
| 6 | Dennis-6 | NULL |
| 7 | Dennis-7 | NULL |
| 8 | Dennis-8 | NULL |
| 9 | Dennis-9 | NULL |
| 10 | Dennis-10 | NULL |
+--+ ——— –+ ——— +

# is it the same as the left join?

>> Direct Join
————–
# If you use right join to select data directly without join, it is the same as the following instructions

code is as follows	copy code
Select a.ID , a.name,b.dept_id from emp A, emp_dept b where a.id=b.emp_id; Mysql> Select a.id,a.name,b.dept_id -> from emp A, emp_dept b -> where a.id=b.emp_id;

+--+ ———-+ ——— +
| ID | name | dept_id |
+--+ ———-+ ——— +
| 2 | Dennis-2 | DEv |
| 1 | Dennis-1 | R&d |
| 3 | Dennis-3 | R&d |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
+--+ ———-+ ——— +

How do you figure it out?

Enjoy it!