Have two tables, a list from a table comparing the other to find the missing records, how to write s-CodePudding

Have two table t1, t2, the table has two columns respectively name, course

The t1 table contains data:
Zhang, c
Xiao zhang, data structure
Xiao liu, the go

T2 table contains data:
Zhang, c
Xiao zhang, data structure
Xiao zhang, a high number
Xiao liu, the go
Xiao liu, perl
A: xiao wang, android

Now to find out t2 table name, same course list is more than the t1 table records:
Xiao zhang, a high number
Xiao liu, perl

CodePudding user response:

 
The select t2. ` name `, t2. ` course ` from t1 inner join t2 on t1. ` name `=t2. ` name ` and not the exists (select 1 from t1 where t2. ` name `=t1. ` name ` and t2. ` course `=t1. ` course `);

CodePudding user response:

 
USE the test; 

DROP TABLE 
IF the EXISTS ` t1 `; 

The CREATE TABLE t1 (
` name ` VARCHAR (10) NOT NULL, 
` course ` VARCHAR (10) NOT NULL 
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

DROP TABLE 
IF the EXISTS ` t2 `; 

The CREATE TABLE t2 (
` name ` VARCHAR (10) NOT NULL, 
` course ` VARCHAR (10) NOT NULL 
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

INSERT a t1 
VALUES 
(' zhang ', 'c'), 
(' zhang ', 'data structure), 
(' liu ', 'go language); 

INSERT t2 
VALUES 
(' zhang ', 'c'), 
(' zhang ', 'data structure), 
(' zhang ', 'advanced mathematics'), 
(' liu ', 'go language), 
(' liu ', 'perl), 
Wang '(',' android '); 

SELECT DISTINCT 
T2. ` name `, 
T2. ` course ` 
The FROM 
T1 
INNER JOIN t2 ON t1. ` name `=t2. ` name ` 
AND NOT the EXISTS (
SELECT 
1 
The FROM 
T1 
WHERE 
T2. ` name `=t1. ` name ` 
AND t2. ` course `=t1. ` course ` 
); 

DROP TABLE t1; 

DROP TABLE t2;

CodePudding user response:

 SELECT * FROM t2 
WHERE t2. The name IN (SELECT the name FROM t1 GROUP BY name) 
AND 
T2. Of course NOT IN (SELECT course FROM t1)

CodePudding user response:

SELECT * FROM t2

WHERE t2. The name IN (SELECT the name FROM t1 GROUP BY name)

AND

T2. Of course NOT IN (SELECT course FROM t1)