How can I determine whether a keyword composed of multiple fields exists in another table?

How can I determine whether a keyword composed of multiple fields exists in another table?

How can I determine whether a keyword composed of multiple fields exists in another table?

Old Shuai (20141107)

1. First, it is easy to determine whether a keyword exists in another table!

SELECT * FROM

WHERE a. ID

IN

(

SELECT B. ID

FROM B

)

2. What if the keyword to be judged has multiple fields?

You cannot use multiple fields IN. Query as follows:

SELECT * FROM

WHERE (a. ID1, a. ID2)

IN

(

SELECT B. ID1, B. ID2

FROM B

)

This does not work normally and violates the SQL Server Standard.

3. To solve this problem, use EXISTS instead of IN!

SELECT * FROM

WHERE EXISTS

(

SELECT NULL

FROM B

WHERE a. ID1 = B. ID1

AND a. ID2 = B. ID2

)

4. It is worth noting that this applies only to IN, not in!

Not in and not exists are slightly different IN the way of processing null values.

SELECT *

FROM

WHERE (a. ID1, a. ID2) NOT IN

(

SELECT B. ID1, B. ID2

FROM B

)

This does not work normally and violates the SQL Server Standard. The query to imitate not in is as follows:

We must use the following query:

SELECT *

FROM

WHERE NOT EXISTS

(

SELECT NULL

FROM B

WHERE a. ID1 = B. ID1

AND a. ID2 = B. ID2

)

AND NOT EXISTS

(

SELECT NULL

FROM B

WHERE B. ID1 IS NULL

OR B. ID2 IS NULL

)

The second predicate ensures that B does not have null values in ID1 and ID2. Any such value will prevent the original query from returning results!

Use the fields of one table as keywords in access to find records that contain these keywords in one field of another table.

I'm dizzy... so complicated... left join...
And there must be something wrong with your results... 4. Where did it come from?

Select Table B. Record, Table A. Details
From table B left join Table
On Table B. Record = Table A. Record

.............
I'm not sure whether access left join and full join can be used...
But I remember it.

In excel, how does one query whether a field contains a keyword in the keyword list?

D1 cell write formula
= COUNT (FIND (A $1: A $5, C1)> 0
Or
= OR (ISNUMBER (FIND (A $1: A $5, C1 )))
Array formula. Press SHIFT + CTRL + ENTER to fill the formula in the drop-down list.