Only display populated categories

[eluser]chipp[/eluser]
Hello CI Friends,

I am working on a project in which I am displaying a list of categories. The categories have items assigned to them.

My Tables:
--------------
Category
--------------
cat_id (PK)
cat_title
--------------

--------------
Item
--------------
item_id (PK)
item_title
cat_id (FK)
--------------

Currently I have a MODEL function (fetch_categories)that retrieves all categories. This is passed to my view from my controller and displays a list of the results. This displays all categories regardless of item assignment.

I would like to modify this to only display categories that have items assigned.

My Question:
How do I validate that categories have items assigned to them and only display these results?

Thank you in advance for any response you may have.

[eluser]GrahamDj28[/eluser]
Here is a query that could help you.

Code:

SELECT c.*, i.*
FROM category AS c
LEFT JOIN item AS i ON i.cat_id = c.id

This should return only categories that have items, and it will also contain the items. If you only want the categories, then remove i.* from the select

[eluser]chipp[/eluser]
[quote author="GrahamDj28" date="1336143695"]Here is a query that could help you.

Code:

SELECT c.*, i.*
FROM category AS c
LEFT JOIN item AS i ON i.cat_id = c.id

This should return only categories that have items, and it will also contain the items. If you only want the categories, then remove i.* from the select[/quote]

Thank you for the quick response. So once I have retreived my query results I could have something like.

Category Item
1 1
1 2
2 3
3 4
3 5
3 6

This would would give me duplicate cat_id's. How can I remove duplicates to retrieve my array of populated cat_id's?

[eluser]boltsabre[/eluser]
Firstly, selecting c.* and i.* returns all the columns of both tables, which in incredibly wasteful, only ever select the columns that you need, I suspect in this case it's just your category id, in which case you just need this:

Code:

SELECT cat_id

Secondly, you want a INNER JOIN, not LEFT JOIN.

LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table

LEFT JOIN will return every category, regardless of if it has an item or not.

Basically, you just want this:

Code:

SELECT c.cat_id
FROM category AS c
INNER JOIN item As i
ON c.cat_id = i.cat_id

There is no need for UNIQUE or anything, as a primary key already has this constraint, this select will only return unique cat_id from the category table where there is an item in the item table with a matching cat_id.

[eluser]chipp[/eluser]
[quote author="boltsabre" date="1336145361"]Firstly, selecting c.* and i.* returns all the columns of both tables, which in incredibly wasteful, only ever select the columns that you need, I suspect in this case it's just your category id, in which case you just need this:

Code:

SELECT cat_id

Secondly, you want a INNER JOIN, not LEFT JOIN.

LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table

LEFT JOIN will return every category, regardless of if it has an item or not.

Basically, you just want this:

Code:

SELECT c.cat_id
FROM category AS c
INNER JOIN item As i
ON c.cat_id = i.cat_id

There is no need for UNIQUE or anything, as a primary key already has this constraint, this select will only return unique cat_id from the category table where there is an item in the item table with a matching cat_id.[/quote]

Would this return, say cat_id 1, multiple times if attached to multiple items?

[eluser]CroNiX[/eluser]
You could always add a DISTINCT or GROUP BY to avoid that.