SQL Question

[Anon]

This seems like a simple question, but the fact that I can't find an answer probably means I'm going about it the wrong way. Here's the lowdown:

In my product database, a product can be associated with multiple versions of a related system. So the database entry could be:

prodID > 27
prodSystemID > 3
prodSystemVersion > 2,6,7

I have a table with details on the individual systems, which is what I'm referencing here. The problem is that I have a page where you can list products by system version...so if the user is looking for the system version who's ID is 6, what is the SQL statement to find it within the "prodSystemVersion" field's string? Somthing in reverse is easy (WHERE prodSystemID IN ('$string') for example) but this one is getting me.

I know my option is to build a seperate table for this relation, but I'd rather not if this is doable. Help?

• JP

[tomhath]

Your option to build the separate table is the preferred solution. What you have here is an array inside a row; that always causes problems...

To find the rows you want with this data you'll need to use RLIKE, search for a regular expression within the column (assuming you are using mysql, most DBMSs don't support regular expressions as well as mysql).