Only display populated categories

[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?