I have three separate tables in my DB. One with a list of companies and another with a list of company_categories. And the last with a like of categories. All rows in the companies and categories table has a unique ID. There are no unique ID's in the company_categories table.
This is a sample of what my companies table looks like:
company_id | company_name | logo | ....
-----------+------------------+-----------+--------
5 | Co 5 | 123.jpg | ....
6 | Company 6 | 123.jpg | ....
7 | 7's a company | 234.jpg | ....
8 | EightCo | 345.jpg | ....
This is a sample of what my categories table looks like:
category_id | category_name
------------+-----------
1 | Blue
2 | Red
3 | Green
4 | Yellow
And what my companies_categories look like:
company_id | category_id
-----------+-----------
5 | 2
5 | 3
6 | 1
6 | 2
7 | 1
Note: company_id, and category_id both have foreign keys in their relating tables
What I am trying to do is list all companies with a certain category. For example if category 2 is chosen companies 5 and 6 should be returned. If category 4 is chosen, nothing should be displayed. Company 8 should never be displayed (because it has no categories associated with it)
I've attempted to solve this issue by using codeigniter's distinct() function in active record:
$this->db->from('company_categories')
->distinct()
->where('category_id', $category_id)
->join('companies', 'companies.company_id = company_categories.company_id')
->select('companies.company_id as co_id companies.logo as co_logo, companies.name as co_name')
->get()->result();
but what this actually does is gets distinct company_id, company_logo, and company_name. So if a company (like company 5 and 6) have the same logo only one of those companies will be returned in the result. I would like for both to show. Can this be done using codeigniter's active record?
If not, how can this be done in an SQL query?
As @Rick says you don't need the distinct. Furthermore you should be selecting from companies, and joining on company_categories not the other way around.
Your SQL should look like so:
SELECT * FROM companies
JOIN companies_categories
ON companies.company_id=companies_categories.company_id
WHERE category_id = 2
Updated sqlfiddle: http://www.sqlfiddle.com/#!2/d1f24/16/0
In codeigniter:
$this->db->select('companies.company_id as co_id, companies.logo as co_logo, companies.name as co_name')
$this->db->where('display_flg',true);
$this->db->join('company_categories', 'companies.company_id = company_categories.company_id');
$query = $this->db->get('companies');