Solved

arrange my sql data

Posted on 2013-01-25
4
255 Views
Last Modified: 2014-05-19
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
Comment
Question by:spectrumcare
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 38821767
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
 

Author Comment

by:spectrumcare
ID: 38827022
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
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 38829625
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
 
LVL 3

Expert Comment

by:stevejacob68
ID: 40076806
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question