Search code examples
phpmysqlcodeigniterpyrocms

Codeigniter database Join on two columns based on multiple values

I want to help in following query:-

    $this->db->select('nbr.*, dm.dma, st.abbrev as state,unit.size_title as ad_size_title,unit_u.size_title as ad_unit_title,di.size_title as ad_di_title,color.title as color_title');
$this->db->from('default_new_buy_request as nbr');
$this->db->join('default_dmadata as dm', 'nbr.dma_id = dm.id', 'left');
$this->db->join('default_states as st', 'nbr.state_id = st.id', 'left');

The nbr.dma_id comes with multiple comma (',') separated values and is going to compared with all dm.id in default_dmadata, but here it just compare only one value with dm.id. In short I need a an explode() type functionality to compare each of value with second column.

Solution

  • Can you try it?

    Use If the values In comma

    In MYSQL, try it with Codeigniter

    LEFT JOIN default_dmadata dm ON FIND_IN_SET(dm.dma_id, dm.id)