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

I do not understand your question! "select data which is found in table and not found both data" - I do not understand what this means! Your sample results only contain the matching records. — Shadow, Sep 26 '16 at 05:30
Look suppose we table whose id is 1,2,3,4,5 and in my procedure i am sending 1,2,9,12 so i want 4 result with 1,value1 |2,value2| 9,null|12,null @ Shadow — Dev Research, Sep 26 '16 at 05:37
Oh, ok, now I got it. You want to return a list with gaps filled. This question has been asked and been answered many times here on SO. — Shadow, Sep 26 '16 at 05:45
Possible duplicate of [MySQL Query that adds blanks to fill the gaps](http://stackoverflow.com/questions/34618443/mysql-query-that-adds-blanks-to-fill-the-gaps) — Shadow, Sep 26 '16 at 05:50
Before you say that the duplicate applies to dates: the logic is the same, list the names in the subquery with the union. — Shadow, Sep 26 '16 at 05:52
But i am unable to Find which data is not Exist in table i mean suppose some value not in table i will get that — Dev Research, Sep 26 '16 at 06:56
This is exactly what the duplicate topic is doing. Fetch values that are not in your main table. — Shadow, Sep 26 '16 at 08:01

score 5 · Accepted Answer · edited May 23 '17 at 12:33

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 |
+-------------+-------+-------+-----------+------+------------+---------+--------+--------+

You can use the 2nd chunk shown by fthiella via his union strategy — Drew, Sep 26 '16 at 07:38
But as I have helper tables here and there used by many routines (mainly dates for `LEFT JOINS`) I figured I would spare the guy that wants that string to contain 100 items. Gotta be realistic about silly union stmts that are unwieldy and don't scale — Drew, Sep 26 '16 at 07:39
yes i need that But i am unable to Do coz i am unable to find whose data not Exist in table @Drew please help me Using union — Dev Research, Sep 26 '16 at 07:46
I just showed you how to do it from your csv of 4. Tom doesn't exist. — Drew, Sep 26 '16 at 07:47
Range allocation gaps that occur with quickly loading tables with InnoDB. It would mess up the id. So I convert at the end. Like [Here](http://stackoverflow.com/a/33666394) at the top. If I didn't do that then the id's are not contiguous. — Drew, Sep 26 '16 at 11:29
But still i am confused about engine can you please explain more — Dev Research, Sep 27 '16 at 04:51
come to [Campaigns](http://chat.stackoverflow.com/rooms/95290/campaigns) chat. Chat all day about it. — Drew, Sep 27 '16 at 04:54
It took a day for the chat servers to get the cache updated reflecting your 20 rep. You appear able to chat away freely in the chat room above. — Drew, Sep 27 '16 at 16:50

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

1 Answers1

Linked

Related