Comparing two comma separated results

[watsmyname]

Hello,

 

I have two queries that extracts result from two tables.  First query fetches a field that has comma separated values. And second query as well fetches a field from another table that has comma separated values. For example

 

first_result_field: 12,34,56,19,5,17,28

second_result_field: 5,12,6,9,13,78,45,11,46

 

now my problem is to get common id(or ids) from these two results. Like we have 5 and 12 common in both results in above example. HOw can i get this via mysql query?

 

Thanks

watsmyname

 

[trq]

Firstly, why are you storing comma separated data in a database? This would be allot simpler if your data was normalised.

 

Anyway, implode your string into arrays then use array_interest.

[watsmyname]

Firstly, why are you storing comma separated data in a database? This would be allot simpler if your data was normalised.

 

Anyway, implode your string into arrays then use array_interest.

 

thanks for the reply, I have used comma separated because lets say I have a products that fall under 100's of categories and subcategories and again there will be millions of products. Storing each  on single row the size of the database will be bulk which i think ultimately reduces the query execution time. And in regard to the solution you offered, in php i know it can be done using array_intersect function, but i have to achieve this in Mysql.

 

watsmyname

[trq]

The only way (I know of) to achieve this within your database would be to use a better database design in the first place.

[fenway]

thanks for the reply, I have used comma separated because lets say I have a products that fall under 100's of categories and subcategories and again there will be millions of products.

Not every product has 100s of categories.