How to select all data whose input array is found and not found in mysql

Question

CREATE TABLE emp ( empno decimal(4,0) NOT NULL, ename varchar(10) default NULL, job varchar(9) default NULL, mgr decimal(4,0) default NULL, hiredate date default NULL, sal decimal(7,2) default NULL, comm decimal(7,2) default NULL, deptno decimal(2,0) default NULL ); INSERT INTO emp VALUES ('7369','SMITH','CLERK','7902','1980-12-17','800.00',NULL,'20'); INSERT INTO emp VALUES ('7499','ALLEN','SALESMAN','7698','1981-02-20','1600.00','300.00','30'); INSERT INTO emp VALUES ('7521','WARD','SALESMAN','7698','1981-02-22','1250.00','500.00','30'); INSERT INTO emp VALUES ('7566','JONES','MANAGER','7839','1981-04-02','2975.00',NULL,'20'); INSERT INTO emp VALUES ('7654','MARTIN','SALESMAN','7698','1981-09-28','1250.00','1400.00','30'); INSERT INTO emp VALUES ('7698','BLAKE','MANAGER','7839','1981-05-01','2850.00',NULL,'30'); INSERT INTO emp VALUES ('7782','CLARK','MANAGER','7839','1981-06-09','2450.00',NULL,'10'); INSERT INTO emp VALUES ('7788','SCOTT','ANALYST','7566','1982-12-09','3000.00',NULL,'20'); INSERT INTO emp VALUES ('7839','KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,'10'); empno ename job mgr hiredate sak comm depno '7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800.00', NULL, '20' '7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600.00', '300.00', '30' '7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30' '7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975.00', NULL, '20' '7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250.00', '1400.00', '30' '7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850.00', NULL, '30' '7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450.00', NULL, '10' '7788', 'SCOTT', 'ANALYST', '7566', '1982-12-09', '3000.00', NULL, '20' '7839', 'KING', 'PRESIDENT', NULL, '1981-11-17', '5000.00', NULL, '10' 

this is the table i want

select the data that is in the table and the data that I want to use to combine or join was not found. I am trying to use this query but cannot do it.

 SELECT * FROM (emp) WHERE FIND_IN_SET(ename, 'SMITH,WARD,KING,TOM') Union All select * from (emp) where !FIND_IN_SET(ename, 'SMITH,WARD,KING,TOM') 

I need when I add the input "SMITH, WARD, KING, TOM"

then it should retrieve the data as follows:

 empno ename job mgr hiredate sak comm depno '7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800.00', NULL, '20' '7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30' '7839', 'KING', 'PRESIDENT', NULL, '1981-11-17', '5000.00', NULL, '10' null , 'TOM' , null ,null, null null null, null 

please suggest me how to do this

I want to create a procedure in such a way as to pass "SMITH, ALLEN, TOM", Then it should give 3 lines. As in this case, 7369 | SMITH, 7499 | ALLEN, null | Tom

Answer 1

The helper table is used for the left join / right join concept, but it was not so simple.

From my answer here (Edit3) Here :

 CREATE TABLE 4kTable ( -- a helper table of about 4k consecutive ints id int auto_increment primary key, thing int null )engine=MyISAM; insert 4kTable (thing) values (null),(null),(null),(null),(null),(null),(null),(null),(null); insert 4kTable (thing) select thing from 4kTable; insert 4kTable (thing) select thing from 4kTable; insert 4kTable (thing) select thing from 4kTable; insert 4kTable (thing) select thing from 4kTable; insert 4kTable (thing) select thing from 4kTable; insert 4kTable (thing) select thing from 4kTable; insert 4kTable (thing) select thing from 4kTable; insert 4kTable (thing) select thing from 4kTable; insert 4kTable (thing) select thing from 4kTable; -- verify: -- select min(id),max(id),count(*) from 4kTable; -- 1 4608 4608 ALTER TABLE 4kTable ENGINE = InnoDB; -- *********** it is now InnoDB 

From the modified answer by User fthiella ... this post is here

 select SUBSTRING_INDEX(SUBSTRING_INDEX(@str, ',', 4k.id), ',', -1) name from 4kTable 4k cross join (select @str:='SMITH,WARD,KING,TOM') vars on CHAR_LENGTH(@str) -CHAR_LENGTH(REPLACE(@str, ',', ''))>=4k.id-1; +-------+ | name | +-------+ | SMITH | | WARD | | KING | | TOM | +-------+ 

Thus, the above is a general form of plcping csv into a query and generating a table from it.

Now make a derivative table ( d ) from the above, combine using RIGHT JOIN with the op code (this diagram was shown in the op code)

 select d.name as rtable_name,e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno from emp e right join ( select SUBSTRING_INDEX(SUBSTRING_INDEX(@str, ',', 4k.id), ',', -1) name from 4kTable 4k cross join (select @str:='SMITH,WARD,KING,TOM') vars on CHAR_LENGTH(@str) -CHAR_LENGTH(REPLACE(@str, ',', ''))>=4k.id-1 ) d on d.name=e.ename; 

Results:

 +-------------+-------+-------+-----------+------+------------+---------+--------+--------+ | rtable_name | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------------+-------+-------+-----------+------+------------+---------+--------+--------+ | SMITH | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | WARD | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | KING | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | TOM | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +-------------+-------+-------+-----------+------+------------+---------+--------+--------+