SQL使用where contains来根据另一个表的内容返回行

I need some help:

我需要一些帮助:

I have a table called Countries, which has a column named Town and a column named Country.

我有一个名为Countries的表,它有一个名为Town的列和一个名为Country的列。

Then I have table named Stores, which has several columns (it is a very badly set up table) but the ones that are important are the columns named Address1 and Address2.

然后我有一个名为Stores的表,它有几列(它是一个非常糟糕的设置表),但重要的是名为Address1和Address2的列。

I want to return all of the rows in Stores where Address1 and Address2 contains the towns in the Countries table.

我想返回商店中的所有行,其中Address1和Address2包含Countries表中的城镇。

I have a feeling this is a simple solution but I just can't see it.

我有一种感觉,这是一个简单的解决方案,但我无法看到它。

It would help if maybe you could use WHERE CONTAINS but in your parameters search in another table's column?

如果您可以使用WHERE CONTAINS但在您的参数中搜索另一个表的列会有所帮助吗?

e.g.

SELECT * 
FROM Stores
WHERE CONTAINS (Address1, 'Select Towns from Countries')

but obviously that is not possible, is there a simple solution for this?

但显然这是不可能的,有一个简单的解决方案吗?

2 个解决方案

#1

You're close

SELECT * FROM Stores s
WHERE EXISTS (
    SELECT * FROM Countries
    WHERE CONTAINS(s.Address1, Town) OR CONTAINS(s.Address2, Town)
)

#2

This would be my first attempt:

这将是我的第一次尝试:

select * from stores s
where 
  exists 
  (
    select 1 from countries c 
    where s.Address1 + s.Address2 like '%'+c.Town+'%'
  )

Edit: Ooops just saw that you want the 'CONTAINS' clause. Then take Paul's solution

编辑:Ooops只是看到你想要'CONTAINS'子句。然后采取保罗的解决方案

#1