One query table

**RFH Reseller: Rappie** · 16th March 2008, 11:54 PM

Guys,

It possible to do all this with just one SQL query?

Let’s say I have a table with health info on all my pets in a MySQL database.

Code:

+--------+---------+--------+
| name   | specie  | health |
+--------+---------+--------+
| Bo Bo  | bird    | good   |
| Diane  | cat     | good   |
| Bells  | Rabbit  | good   |
| Lizie  | cat     | good   |
| Benny  | dog     | ill    |
| Gwen   | hamster | good   |
| Roger  | Rabbit  | good   |
| Harold | dog     | good   |
| Smoky  | bird    | good   |
| Clio   | Rabbit  | good   |
+--------+---------+--------+

I am trying to create a page to show all the pets info on. The pet info are ordered and grouped by animal species in a HTML table with a header showing the specie and a health summary. If all goes well it should look something like this:

Code:

+--------+--------+
|  birds      ok  |     <----- header, shows specie and health summary, all birds are fine so the health summary is: ok
+--------+--------+
| Smoky  | good   |
| Smoky  | good   |
| Bo Bo  | good   |
+--------+--------+
|  cats       ok  |
+--------+--------+
| Diane  | good   |
| Lizie  | good   |
+--------+--------+
|  dogs   -=NOK=- |     <----- One poor dog is ill so the health summary should return: nok
+--------+--------+
| Harold || good  |
| Benny  |  ill   |
+--------+--------+
|  hamster    ok  |
+--------+--------+
| Gwen   | good   |
+--------+--------+
|  rabbits    ok  |
+--------+--------+
| Roger  | good   |
| Bells  | good   |
| Clio   | good   |
+--------+--------+

I am close to having all of this in my script using just one SQL query. I only heaving trouble with the health summary in the header of the table. Is there a way summarize the health of a group of species?

This my script so far: (its in ASP, any pointer of solution in PHP is just as usefull)

Code:

Set RS = objDBConn.Execute("SELECT name,specie,health FROM `pets` ORDER BY specie, name ASC")
while not RS.eof
 If strServerType <> RS("specie") Then
  i=0
 End if   
 
 If i = 0 Then
   Response.write(RS("specie") & "health summary: ? " & vbCrLf) 
 End If
 
   Response.write(RS("name") & " - " RS("health") & vbCrLf) 
 
i = i + 1
strServerType = RS("specie") 
RS.MoveNext
wend

I think I am close, i've got everything but the health summary.

**RFH Reseller: Wise Webs** · 17th March 2008, 08:56 AM

Try this code...

Code:

SELECT 
1 as header, 
specie,
name,
health 
FROM animals 
UNION ALL 
SELECT 
0 as header, 
specie,
specie,
CASE WHEN MAX(health) = 'ill' THEN '-=NOK=-' ELSE 'OK' END
FROM animals 
GROUP BY specie
ORDER BY specie, header, name ASC