sql - how to select all data whose input array 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 table i want

select data which is found in table and not found both data i want using join or union i am trying use this query but unable to do.

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 want when i put input 'SMITH,WARD,KING,TOM'

then it should retrun data like this:

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 ac-chive this

i want to create Procedure such way so that i will pass 'SMITH,ALLEN,TOM' Then it should give 3 row Like in this manner 7369|SMITH,7499|ALLEN,null|TOM

Answer 1

A helper table is used for the left join / right join concept but it was not quite as 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 a modified answer from User fthiella ... that post 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   |
+-------+

So the above is the generic form of plopping a csv into a query and generating a table out of it.

Now make a derived table (d) out of the above, combine via RIGHT JOIN with op code (that schema was shown in 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 |
+-------------+-------+-------+-----------+------+------------+---------+--------+--------+