retrieve comma delimited data from a field in using SQL

Question

I've created a form in PHP that collects basic information. I have a list box that allows multiple items selected (i.e. housing, rent, food, water). If multiple items are selected they are stored in a field called Needs separated by a comma.

I have created a report ordered by the persons needs. The people who only have one need are sorted correctly, but the people who have multiple are sorted exactly as the string passed to the database (ie. housing, rent, food, water) --> which is not what i want.

Is there a way to separate the multiple values in this field using SQL to count each need instance/occurrence as 1 so that there are no comma delimitations shown in the results?

CLICK FOR EXAMPLE

Answer 1

I'm a little confused by the goal. Is this a UI problem or are you just having trouble determining who has multiple needs?

The number of needs is the difference:

Len([Needs]) - Len(Replace([Needs],',','')) + 1

Can you provide more information about the Sort you're trying to accomplish?

UPDATE: I think these Oracle-based posts may have what you're looking for: post and post. The only difference is that you would probably be better off using the method I list above to find the number of comma-delimited pieces rather than doing the translate(...) that the author suggests. Hope this helps - it's Oracle-based, but I don't see .

Answer 2

Your database is not in the first normal form. A non-normalized database will be very problematic to use and to query, as you are actually experiencing.

In general, you should be using at least the following structure. It can still be normalized further, but I hope this gets you going in the right direction:

CREATE TABLE users (
    user_id int,
    name    varchar(100)
); 

CREATE TABLE users_needs (
    need     varchar(100),
    user_id  int
);

Then you should store the data as follows:

-- TABLE: users

+---------+-------+
| user_id | name  |
+---------+-------+
|       1 | joe   | 
|       2 | peter | 
|       3 | steve | 
|       4 | clint | 
+---------+-------+

-- TABLE: users_needs

+---------+----------+
| need    | user_id  |
+---------+----------+
| housing |       1  | 
| water   |       1  | 
| food    |       1  | 
| housing |       2  | 
| rent    |       2  | 
| water   |       2  | 
| housing |       3  | 
+---------+----------+

Note how the users_needs table is defining the relationship between one user and one or many needs (or none at all, as for user number 4.)

To normalise your database further, you should also use another table called needs, and as follows:

-- TABLE: needs

+---------+---------+
| need_id | name    |
+---------+---------+
|       1 | housing | 
|       2 | water   | 
|       3 | food    |   
|       4 | rent    | 
+---------+---------+

Then the users_needs table should just refer to a candidate key of the needs table instead of repeating the text.

-- TABLE: users_needs (instead of the previous one)

+---------+----------+
| need_id | user_id  |
+---------+----------+
| 1       |       1  | 
| 2       |       1  | 
| 3       |       1  | 
| 1       |       2  | 
| 4       |       2  | 
| 2       |       2  | 
| 1       |       3  | 
+---------+----------+

You may also be interested in checking out the following Wikipedia article for further reading about repeating values inside columns:

• Wikipedia: First normal form - Repeating groups within columns

---

UPDATE:

To fully answer your question, if you follow the above guidelines, sorting, counting and aggregating the data should then become straight-forward.

To sort the result-set by needs, you would be able to do the following:

SELECT     users.name, needs.name
FROM       users
INNER JOIN needs ON (needs.user_id = users.user_id)
ORDER BY   needs.name;

You would also be able to count how many needs each user has selected, for example:

SELECT     users.name, COUNT(needs.need) as number_of_needs
FROM       users
LEFT JOIN  needs ON (needs.user_id = users.user_id)
GROUP BY   users.user_id, users.name
ORDER BY   number_of_needs;