Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

CodeIgniter GROUP_CONCAT and join

Tags:

I am trying to figure a way to join these two tables together, which I was able to do, but if it found more than one value that matched, it showed everything from the product table again. Now I am trying to use the MySQL group_concat together to be able to list all of tName in one field in the array but I keep getting an error with MySQL:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (sp_product) LEFT OUTER JOIN sp_product_type ON sp_product_type.`tCat' at line 2

SELECT sp_product.name, sp_product.price, sp_product.perm_name, sp_product.description, GROUP_CONCAT(product_type.tName SEPARATOR FROM (sp_product) LEFT OUTER JOIN sp_product_type ON sp_product_type.tCategory = sp_product.type WHERE perm_name = 'bacon'

$this->db->select('product.name, product.price, product.perm_name, product.description, GROUP_CONCAT(product_type.tName SEPARATOR ',') as product_type.tName'); 
$this->db->from('product');
$this->db->where('perm_name', $this->uri->segment(2));
$this->db->join('product_type', 'product_type.tCategory = product.type', 'LEFT OUTER');
$query = $this->db->get(); 

Any ideas what I am doing wrong?