Esmail Yahya Gathuth Esmail Yahya Gathuth - 1 month ago 6
MySQL Question

select multiple columns from another table where field contains array

i have tow table

phi_ads {id,files}
phi_files {id,file}

the problem is phi_ads contains array for example (20,21) this numbers is id for phi_files

so i need to select all this files

i try this

select a.name,d.*,f.* from phi_ads d
inner join phi_areas a on a.id = d.area
inner join phi_files f on f.id = d.files
where d.id=42


for note phi_ads.files = "102,103" so return only first file , but i need to return all file in phi_files

sorry i am new , sorry for my english to

Answer

Use IN operator , The IN operator allows you to specify multiple values in a WHERE clause.

  phi_ads.files In (102,103)

Reference

Update

   select a.name,d.*,f.* from phi_ads d 
   inner join phi_areas a on a.id = d.area 
   inner join phi_files f on FIND_IN_SET( f.id , d.files )
   where d.id=42

Update 2

   select a.name,d.*,GROUP_CONCAT(f.name) as name from phi_ads d 
   inner join phi_areas a on a.id = d.area 
   inner join phi_files f on FIND_IN_SET( f.id , d.files )
   where d.id=42
Comments