Select a value from one table based on values in another table

I have two tables:

products with a ‘capacity’ field which is a comma delimited list of numbers corresponding to 'id’s in the
properties table

I tried:

SELECT value
FROM properties
WHERE id IN (SELECT capacity
             FROM products
             WHERE id = '$key')

which brings up a single record, the first one in the list. The sub-query is bringing up the right data 22, 24, 26, 28. When I try

SELECT value
FROM properties
WHERE id IN (22, 24, 26, 28)

I get all four rows! What am I missing? :confused:

Technically, yes, but someone has to foot the bill. The owner of the web-app is a small business that won’t be spending money on normalizing data. :slight_smile:

Again, better solution is to normalize your data.

At this stage of the game normalizing these tables would be a major undertaking and the client isn’t paying for that. :cool:

FIND_IN_SET() seems to work on strings only. I could not figure out how to use it to solve this problem.

But this works:

SELECT capacity
FROM products
WHERE id = '$key'
$capacities = mysql_result($result, 0);
SELECT value
FROM properties
WHERE id IN ($capacities)

What you are actually receiving with your query would be something like this:


SELECT value
FROM properties
WHERE id IN ('22,56,78,1', '34,1,24', '26,456,7', '61,28')

Option 1: Normalize your data properly so that each capacity is in a single row of a another table with a the foreign key to the property it belongs to. Than you will be able to use a construct similar to what you have.

Option 2: FIND_IN_SET()