• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

arrange my sql data

I am creating a report to show the break down of people symptoms. The symptom field is comma separated.  I have tried quit a few things with  sql but I can't get the data the way I want it.
 
                             Table A
symptom                                  ID
Cold,Flu,Pink Eye                       18
Cold                                           22
Flu,Bloody Nose                        23
Flu, Pink Eye, Bloody Nose        24

What I want to get is the count of how many people have colds and their ID, how many people have Flu and their ID  like
Cold 2 18,22
Flu 3 18,23,24
Is their a way of doing this?
0
spectrumcare
Asked:
spectrumcare
  • 2
1 Solution
 
johanntagleCommented:
Your main problem is the way your data is stored - it is hard to generate reports the way it was done.  It should have been stored liked this:

symptom                                  ID
Cold                                         18
Flu                                            18
Pink Eye                                    18
Cold                                           22
Flu                                        23    
Bloody Nose                        23
Flu                                        24
Pink Eye                               24
Bloody Nose                        24

The above is the standard way to store data with many-to-many relationship.  If you had it done that way you could have done this:

select symptom, count(id), group_concat(id) from table_name group by symptom;

Rather than developing a complicated workaround I suggest you focus on fixing your data structure - it will solve many other things along the way.
0
 
spectrumcareAuthor Commented:
I do agree but this data is being stored is coming from a text area box. The user types in the symptom and they can edit this when they want.  To do a database change for 1 report is not worth changing multiple pages for it. I did however come up with a way of doing it on the week end.  It is probably not the best way of doing it but it is a way.
I loop my query getting the symptoms splitting the sting and putting the values into an array. I then loop the array querying the original query to find the symptom that matches. The record count of the query gives me how many people have the symptom and I have their ID's to get their name.
0
 
johanntagleCommented:
So what happens if the user forgets to separate symptoms with a comma (I assume that's the delimiter you use)?  Your report might contain something like:

Pink Eye     5     10,11,12,13,14
Pink Eye Bloody Nose     1      15
Bloody Nose      3      16,17,18

If the client can live with that, then fine.  But if I foresee more complicated requirements later on I would recommend revisiting the database design.
0
 
stevejacob68Commented:
Hi,
Try this statement:
Select * from table_name where symptom=”cold” or symptom=”flu” or symptom=”pink” or symptom=”eye”
And write other statement like this to generate other output
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now