CODEWITHSUNDEEP
sqlsql-server
Hanno Opperman
Hanno Opperman

Reputation: 143

SQL using where contains to return rows based on the content of another table

I need some help:

I have a table called Countries, which has a column named Town and a column named 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.

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

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?

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?

Upvotes: 1

Views: 49

Answers (2)

alzaimar
alzaimar

Reputation: 4622

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

Upvotes: 1

Paul Draper
Paul Draper

Reputation: 83255

You're close

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

Upvotes: 2

Related Questions