How to filter a list of items in a MySQL database?

Question

I have list store in mysql table file1=(1,2,3,4,6,7) and other list file2 = (3,2,4,8,9,10,12) is not stored in table, i want compare both and result should be like result=(6,7,8,9,10,12) then calculate the percentage. like 100*(result/file1+file2) in mysql data structure. i do not know how i will do it. please know body know guide me or give me a small example. thanks

Answer 1

I don't think you can easily do that in pure sql. you need a procedural language to get the result in the first list, merge, sort, unique with the second list and compute the result.

Answer 2

Create a temporary table to store the "other" list that is not already in a table, and then use a join or union to get the result you want.

If you are wanting to select the values that are not in either list, then you can use GROUP BY and COUNT to count the occurrence of each number, and then HAVING to select those rows with a count of 1.