Solved

arrange my sql data

Posted on 2013-01-25
4
252 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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 …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

813 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now