INNER JOIN LIKE? 1

[SaRiD]

I've come across a problem that I haven't faced before and was wondering if it can be done.

Basicly the SQL goes like this:

SELECT.... FROM [tablea] INNER JOIN [tableb] ON [tablea].[column] LIKE [tableb].[column]

[tablea].[column] = "example"
[tableb].[column] = "example, example2"

So I want to do a normal table join, except I want it to pick up the records that match but arn't EXACT.

So [tableb].[column] should be picked up if [tablea].[column] had "example"

Can be done???

 

[TonyGroves]

You could use:
[tt]
ON tableb.column LIKE CONCAT('%',tablea.column,'%')
[/tt]

 

[SaRiD]

It executed ok, but still didn't bring up the right rows.

Any other ideas?

 

[TonyGroves]

You'll have to tell us more. What do you mean by matching? My solution will match wherever the value of tablea.column is contained within the string tableb.column. If your requirements are more complicated than that, then please explain.

 

[SaRiD]

Thats exactly what I'm after, but it just didn't bring up the data it should have.

The data in columnb = "Sport Activities"
the data in columna = "Lesuire Activities,Sport Activities"

So where columnb matches the string in columna join that row.

I'm listing activities. Each activity has a 1 or more main categories listed against each avtivity seperated by a comma E.g. Skiing is under Lesuire Activities and Sport Activities.

There is a table containing the main categories. So I am wanting to select all the main categories and all the activities that match the main category name.

So the main category has a row called "Sports Activities", and that should match an activity with a main activity containing the text "Sports Activities", or in this case "Lesuire Activities,Sport Activities"

Does that help?

 

[r937]

any chance you can redesign your tables?

searching in a comma-delimited list means a table scan, so your app will slow down the bigger it gets

nobody wants to build an app that will choke if it gets popular, right?

r937.com | rudy.ca

 

[TonyGroves]

Rudy is right about your table design, but for the moment we'll work on the current problem.

There seems to be some confusion about which table contains which string. The solution I gave assumes that tableb.column contains the multi-element string and tablea.column contains the single-element string. You would really have to be a bit clearer about your table structures.

Another thought: the solution I gave is a bit simplistic; you might be better off using the FIND_IN_SET function, which is designed for your situation:[tt]
ON FIND_IN_SET(tablea.column,tableb.column)
[tt]

 

[SaRiD]

Perfect thank you TonyGroves!

I agree, I should really create a sperate table containing the activity id and the category id. I am always curious when I come across a new problem to see if there is an answer to it cause you never know when it might come in handy.

Thank you all for your help.