Select statement using DISTINCT?

scoyle74

I am having a problem getting the select statement to return the values I need in the format that I would like. I have looked in the mysql manual and in other forums but I am either still confused or don't understand. Hopefully someone will be able to help me.😕

Okay here goes....

I have three tables

table a :
staffid name dept
ex) 4 john 25

table b:
staff_id sect_id question answer
ex) 4 s1 a Yes
4 s1 b No
4 s2 a Yes
4 s2 b Yes
4 s3 a Yes

table c :
sect_id sect_title
s1 Jump
s2 Run
s3 Walk

The keys/indexes are:

table a - staffid(primary)
table b - staff_id / sect_id (foreign)
table c - sect_id(primary)

The output I would like is this (I have the name and dept select query separate so that is not the problem)

Name : John
Dept : 25

Title question answer
Jump a Yes
b No
Run a Yes
b Yes
etc....

The select that I am using is

SELECT DISTINCT c.sect_title, b.question, b.answer
FROM c, b, a
WHERE c.sect_id = b.sect_id
AND b.staff_id = a.staffid
AND a.staffid = 4

The output I am getting is :

Name : John
Dept : 25

Title question answer
Jump a Yes
Jump b No
Run a Yes
Run b Yes
etc.....

I have tried putting the DISTINCT in ()brackets, as well as using the Group By but that doesn't work either. I am even thinking about separating all the queries and trying to format the output using PHP the way I would like. I think I might be close but am not sure, anyone have an idea?

thanks,
Scott

barand

SELECT c.sect_title, b.question, b.answer
FROM c, b, a
WHERE c.sect_id = b.sect_id
AND b.staff_id = a.staffid
AND a.staffid = 4

The distinct won't do it. The trick is in the procesing of the results.

$last = '';
while (list($t, $q, $a) = mysql_fetch_row($result)) {
   echo "<tr>";
   if ($t != $last) 
       echo "<td>$t</td>";
   else
       echo "<td>&nbsp;</td>";
   $last = $t;
   echo "<td>$q</td>";
   echo "<td>$a</td>";
   echo "</tr>";
}

hth

scoyle74

barand, thanks it works like a charm. I knew it would be simple for someone😉

thanks,
Scott