Get all Categories from Database

[macleodjb]

Hi Guys,

I was hoping you could tell me how to retrieve all categories from a database table.

For instance i have a table with a field called categories. I'd like to output all the different categories into a list element. Don't know how it's done.

Thanks in advance.

[ptmuldoon]

Do you want it list ALL the items in that field, or do you want to remove any duplicates?

[macleodjb]

I want to remove any duplicates

[Leeoniya]

SELECT DISTINCT categories FROM tablename

this is not the best way to do it for a large number of records or complicated queries because the SQL engine will get everything first and then filter out duplicates with a second pass (that requires an implicit sort) rather than ignoring/excluding duplicates as it encounters them (after the first occurrence) on the first pass.

see details here:
http://www.databasejournal.com/features/postgresql/article.php/3437821

if you don't have a huge number of records or a complicated query, using DISTINCT will definitely leave your code more readable and easier to understand.

Leon

[macleodjb]

I have about 28,000 records. How do you think i can do it?

[StupidRalph]

You would want your database normalized. You should have a category table that list all the categories without any duplicates. Any other table that uses categories should use this table as a reference.

[Leeoniya]

normalization is usually beneficial, but it depends on a lot of things. With normalization you will have a smaller database, but it can also result in much larger queries since you may need to have many joins to get the view/recordset you need. Creating larger multi JOIN or nested JOIN queries leave a lot of room for intoducing new inefficiencies to the inexperienced SQL programmer. Indexing (fulltext or otherwise) is also very important if you ever use "ORDER BY" or "LIKE" on fields containing text.

there are many factors for determining whether a specific SQL statement is adequate for your needs. SQL can do some impressive things, and inefficient queries only become apparent under heavy load. If your use if for an internal webapp which will not see more than a query every few seconds, you will not see much of a difference with an optimized query unless you're doing live filtering w/AJAX or dynamic sorting, otherwise your optimized query can be many times more difficult to follow if you ever need to change it.

there is a happy medium for everything.

Leon