How to find data not present in a column with respect to preset data in another table's column

问题: I need an SQL code to compare data that doesn't exist in one table with respect to another table that holds the set of unique expected data. I have tried using EXIST and...

问题:

I need an SQL code to compare data that doesn't exist in one table with respect to another table that holds the set of unique expected data.

I have tried using EXIST and NOT EXIST operators.

The EXIST operator will display all data in the second table. The NOT EXIST operator will not display any results.

The code looks something like

SELECT * 
FROM st 
WHERE EXISTS 
(SELECT data FROM udt WHERE st.data <> udt.data) 

This is my unique data table (udt) example:

+------+
| data |
+------+
|   1  |
|   2  |
|   3  |
|   4  |
+------+

This is my second table (st) example:

+------+------+
| name | data |
+------+------+
|  AZ  |   1  |
|  AZ  |   3  |
|  BY  |   2  |
|  BY  |   4  |
|  CX  |   1  |
|  CX  |   4  |
|  DW  |   2  |
|  DW  |   3  |
+------+------+

I would like to have a code that will display the name and the code that don't exist in the second table. That is:

+------+------+
| name | data |
+------+------+
|  AZ  |  2   |
|  AZ  |  4   |
+------+------+ 

and so on

For one, will this be possible? If it is possible, what code syntax can I apply? I get why EXIST and NOT EXIST will not display the data, but is there any way I can get the required results?


回答1:

Not sure if this is the best way of doing this, it's still early and not much coffee yet :o)

I've changed your field names, but kept them as close as possible to yours.

select q1.[name_],q1.data1
from
(select distinct [name_],data1 from 
st,udt) as q1
left join
st as d1
on q1.[name_]=d1.[name_] and
q1.data1=d1.[data_]
where d1.[name_] is null

Gives you the results

name data1

az 2

az 4

by 1

by 3

cx 2

cx 3

dw 1

dw 4


回答2:

Generate all the combinations of name and data and then remove the ones that are in the second table.

In almost any database, you can do this as:

select n.name, t1.data
from (select distinct name from table2) n cross join
     table1 t1 left join
     table2 t2
     on t2.name = n.name and t2.data = t1.data
where t2.data is null;

MS Access does not support this syntax, so you can do:

select n.name, t1.data
from (select distinct name from table2) n, 
     table1 t1
where not exists (select 1
                  from table2 t2
                  where t2.name = n.name and t2.data = t1.data
                 );
  • 发表于 2019-02-20 02:34
  • 阅读 ( 157 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除