How to select where a column contains a column!

[shutterace]

Hi,

I can't figure this out. I am trying to select all records from a table(formatted) where one of its columns contains the value of a column in another table(paths). The data in the second table is a substring of the data in the first. Confused yet? Here is one example of what I am trying.

SELECT *
FROM formattted
WHERE CONTAINS (formatted.path, paths.path)

I just can't seem to get the syntax correct.

Thanks!
-Jim

 

[calidrake]

Is this what you were looking for?

SELECT *
FROM formatted join path
on formatted.path = path.path

 

[tlbroadbent]

Actually, if you are dealing with substrings, you'll want to do something like the following. If the tables a very large, performance could be a problem.

This may also return unwanted rows because it is not selecting based on an exact match or JOIN criteria. Multiple rows in the Formatted table may match a single row in the paths table.

SELECT *
FROM formattted, paths
WHERE charindex(paths.path, formatted.path)>0 Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[shutterace]

Terry,

That worked!

Thanks!
-Jim