Filter qry to get data according to Another Table

Question

This is query to get all categories available in database.

$sql = "SELECT cp.category_id AS category_id, GROUP_CONCAT(cd1.name ORDER BY cp.level SEPARATOR '  >  ') AS name, c1.parent_id, c1.sort_order 
    FROM " . DB_PREFIX . "category_path cp 
    LEFT JOIN " . DB_PREFIX . "category c1 ON (cp.category_id = c1.category_id) 
    LEFT JOIN " . DB_PREFIX . "category c2 ON (cp.path_id = c2.category_id) 
    LEFT JOIN " . DB_PREFIX . "category_description cd1 ON (cp.path_id = cd1.category_id) 
    LEFT JOIN " . DB_PREFIX . "category_description cd2 ON (cp.category_id = cd2.category_id) 
    WHERE cd1.language_id = '" . (int)$this->config->get('config_language_id') . "' AND 
    cd2.language_id = '" . (int)$this->config->get('config_language_id') . "'";

to get this output

So, i am trying to get only that categories listed in rma_mapping_category(this has category_id column)

so i tried this, but syntax got wrong by adding this(AND rma_mapping_category rma)... /////////////////////

$sqs = "SELECT cp.category_id AS category_id, GROUP_CONCAT(cd1.name ORDER BY cp.level SEPARATOR '  >  ') AS name, c1.parent_id, c1.sort_order 
        FROM " . DB_PREFIX . "category_path cp AND rma_mapping_category rma
        LEFT JOIN " . DB_PREFIX . "category c1 ON (cp.category_id = c1.category_id) 
        LEFT JOIN " . DB_PREFIX . "category c2 ON (cp.path_id = c2.category_id) 
  ->    LEFT JOIN " . DB_PREFIX . "category c3 ON (rma.categoryid = c3.category_id) 
        LEFT JOIN " . DB_PREFIX . "category_description cd1 ON (cp.path_id = cd1.category_id) 
        LEFT JOIN " . DB_PREFIX . "category_description cd2 ON (cp.category_id = cd2.category_id) 
        WHERE cd1.language_id = '" . (int)$this->config->get('config_language_id') . "' AND 
        cd2.language_id = '" . (int)$this->config->get('config_language_id') . "'";

Also tried this... ////////////////////// by this i am getting all categories rather than just rma_mapping_category

Same result as First Query.

$sqs = "SELECT cp.category_id AS category_id, GROUP_CONCAT(cd1.name ORDER BY cp.level SEPARATOR '  >  ') AS name, c1.parent_id, c1.sort_order 
        FROM " . DB_PREFIX . "category_path cp
        LEFT JOIN " . DB_PREFIX . "category c1 ON (cp.category_id = c1.category_id) 
        LEFT JOIN " . DB_PREFIX . "category c2 ON (cp.path_id = c2.category_id) 
        LEFT JOIN rma_mapping_category rma ON (cp.category_id = rma.category_id) 
        LEFT JOIN " . DB_PREFIX . "category_description cd1 ON (cp.path_id = cd1.category_id) 
        LEFT JOIN " . DB_PREFIX . "category_description cd2 ON (cp.category_id = cd2.category_id) 
        WHERE cd1.language_id = '" . (int)$this->config->get('config_language_id') . "' AND 
        cd2.language_id = '" . (int)$this->config->get('config_language_id') . "'";

Any suggestion, what i sould try? Any help will be appreciated.

Answer 1

Query worked as expected after adding ==

INNER JOIN rma_mapping_category rma ON (cp.category_id = rma.category_id) like below

    $sql = "SELECT cp.category_id AS category_id, GROUP_CONCAT(cd1.name ORDER BY cp.level SEPARATOR '  >  ') AS name, c1.parent_id, rma.type_id, c1.sort_order 
    FROM " . DB_PREFIX . "category_path cp
    LEFT JOIN " . DB_PREFIX . "category c1 ON (cp.category_id = c1.category_id) 
    LEFT JOIN " . DB_PREFIX . "category c2 ON (cp.path_id = c2.category_id) 
    LEFT JOIN " . DB_PREFIX . "category_description cd1 ON (cp.path_id = cd1.category_id) 
    LEFT JOIN " . DB_PREFIX . "category_description cd2 ON (cp.category_id = cd2.category_id)
    INNER JOIN rma_mapping_category rma ON (cp.category_id = rma.category_id) 
    WHERE cd1.language_id = '" . (int)$this->config->get('config_language_id') . "' AND 
    cd2.language_id = '" . (int)$this->config->get('config_language_id') . "'";