E235 E235 - 29 days ago 6
MySQL Question

Get unique rows that appear in one table but not in other table by checking two columns

I have three tables:

File, Module and Function.

I have functions from file mssip32.dll and functions from file ncsi.dll.

I want to get all the functions that appear in mssip32.dll but not in nsci.dll

IMPORTANT:

Functions are different if their names or their modules are different.

For example:

ModuleA.CreateProcess != ModuleB.CreateProcess (because they have different module)

ModuleA.CreateProcess != ModuleA.Sleep (because have different function name)

ModuleA.CreateProcess == ModuleA.CreateProcess (equals)


The functions from mssip32.dll and nsci.dll as views:

enter image description here

All the tables:

enter image description here

This is what I tried to do:

SELECT fu.function_name AS 'Imported Function', m.module_name AS 'Library', COUNT(fu.function_name) AS 'Usage Count'
FROM File as f,
Module as m,
Function as fu
WHERE f.listview_name like 'listView1'
AND f.file_id = m.file_id
AND f.file_id = fu.file_id
AND m.module_id = fu.module_id
AND fu.function_name NOT in (
SELECT fu2.function_name
FROM File as f2,
Module as m2,
Function as fu2
WHERE f2.listview_name like 'listView2'
AND f2.file_id = m2.file_id
AND f2.file_id = fu2.file_id
AND m2.module_id = fu2.module_id
)
GROUP BY fu.function_name
ORDER BY COUNT(fu.function_name) DESC


But the problem with this one, is that it doesn't check that the module names are different and the result is incorrect:

enter image description here

We are missing
ModuleA.CreateProcess
because it doesn't appear in 'listView2'.

I wanted to be able to try something like this (theoretically):

...
AND (fu.function_name AND m.module_name) NOT in (
SELECT fu2.function_name, m2.module_name
FROM File as f2,
Module as m2,
Function as fu2
WHERE f2.listview_name like 'listView2'
AND f2.file_id = m2.file_id
AND f2.file_id = fu2.file_id
AND m2.module_id = fu2.module_id
)
...


So if I have:

fu.function_name = "CreateProcess"
m.module_name = "ModuleA"

fu2.function_name = "CreateProcess"
m2.module_name = "ModuleB"


It will return
((fu.function_name != fu2.function_name) OR (m.module_name != m2.module_name))


And then the desierd result will be:

enter image description here

CODE:

Table structures:

CREATE TABLE File (
file_id INTEGER NOT NULL ,
name VARCHAR(160) NOT NULL ,
listview_name VARCHAR(30) NOT NULL ,
type VARCHAR(10) NOT NULL ,
recursive VARCHAR(10) NOT NULL ,
PRIMARY KEY (file_id)
) ;

CREATE TABLE Module (
file_id INTEGER NOT NULL ,
module_id INTEGER NOT NULL ,
module_name VARCHAR(30) NOT NULL ,
PRIMARY KEY (file_id)
) ;

CREATE TABLE Function (
file_id INTEGER NOT NULL ,
module_id INTEGER NOT NULL ,
function_name VARCHAR(30) NOT NULL ,
PRIMARY KEY (file_id)
) ;


Views structures:

CREATE VIEW function_List1 AS
SELECT fu.function_name, m.module_name
FROM File as f,
Module as m,
Function as fu
WHERE f.listview_name like 'listView1'
AND f.file_id = m.file_id
AND f.file_id = fu.file_id
AND m.module_id = fu.module_id

CREATE VIEW function_List2 AS
SELECT fu2.function_name, m2.module_name
FROM File as f2,
Module as m2,
Function as fu2
WHERE f2.listview_name like 'listView2'
AND f2.file_id = m2.file_id
AND f2.file_id = fu2.file_id
AND m2.module_id = fu2.module_id


EDIT:

I found another way to solve it.

I can query listview2 to bring me all the functions with the same module the function from listview1 have and then check if function from listview1 is in that list.

I did it by adding
AND m.module_name = m2.module_name
:

AND fu.function_name NOT in (
SELECT fu2.function_name
FROM File as f2,
Module as m2,
Function as fu2
WHERE f2.listview_name like 'listView2'
AND f2.file_id = m2.file_id
AND f2.file_id = fu2.file_id
AND m2.module_id = fu2.module_id
AND m.module_name = m2.module_name)

Answer

Your condition will work if you replace and with ,

AND (fu.function_name, m.module_name) NOT in (
                    SELECT fu2.function_name, m2.module_name
                    FROM File as f2,
                         Module as m2,
                         Function as fu2
                    WHERE f2.listview_name like 'listView2'
                    AND f2.file_id = m2.file_id
                    AND f2.file_id = fu2.file_id
                    AND m2.module_id = fu2.module_id
                    )

You can change to:

WHERE NOT EXISTS 
      ( SELECT 1
        FROM File as f2,
                             Module as m2,
                             Function as fu2
                        WHERE f2.listview_name like 'listView2'
                        AND f2.file_id = m2.file_id
                        AND f2.file_id = fu2.file_id
                        AND m2.module_id = fu2.module_id
                        AND fu2.function_name = fu.function_name
                        AND m.module_name = m2.module_name
      ) ;