Query to not show repeating records

[Tezread]

I have four tables:

supportactivity
supportactvity_id (PK)
supportactivity

qualityadmin
qualityadmin_id (PK)
quality_name

supportactivityrelations
relation_id (PK)
supportactivity_id
qualityadmin_id
quality_name
supportactivity

supportactivityuser
supportactivityuser_id (PK)
supportactivity_id
username
timentered

In supportactivityrelations you can see that each qualityadmin has one or more supportactivities related to it.

I have created a query that works perfectly

$sql1 = "SELECT supportactivity.supportactivity_id AS supportactivity_id, supportactivity.supportactivity AS supportactivity, 
supportactivityuser.username AS username, qualityadmin.quality_name AS quality_name, 
qualityadmin.qualityadmin_id AS qualityadmin_id
FROM supportactivity 
LEFT JOIN supportactivityrelations ON supportactivity.supportactivity_id = supportactivityrelations.supportactivity_id 
LEFT JOIN supportactivityuser ON supportactivity.supportactivity_id = supportactivityuser.supportactivity_id 
LEFT JOIN qualityadmin ON supportactivityrelations.quality_name = qualityadmin.quality_name 
WHERE supportactivityuser.username = '$username' ORDER BY supportactivity"; 

this creates the following results:

http://www.mindseyemidlands.co.uk/leftjoin.gif

This is great but the problem is when I echo results it is showing up the quality_name more than once. Below is my code that shows the above query but in the context of it being echoed with a list of the supportactivities relating to each quality_name

<?php
//display list of organisations that does support activities chosen in form
$sql1 = "SELECT supportactivity.supportactivity_id AS supportactivity_id, supportactivity.supportactivity AS supportactivity, 
supportactivityuser.username AS username, qualityadmin.quality_name AS quality_name, 
qualityadmin.qualityadmin_id AS qualityadmin_id
FROM supportactivity 
LEFT JOIN supportactivityrelations ON supportactivity.supportactivity_id = supportactivityrelations.supportactivity_id 
LEFT JOIN supportactivityuser ON supportactivity.supportactivity_id = supportactivityuser.supportactivity_id 
LEFT JOIN qualityadmin ON supportactivityrelations.quality_name = qualityadmin.quality_name 
WHERE supportactivityuser.username = '$username' ORDER BY supportactivity"; 

$results1 = mysql_query($sql1); 
while ($row = mysql_fetch_array ($results1)){ 
//list of support activities relating to each organisation
$name1 = $row["quality_name"]; 

echo '
<table cellpadding="0" cellspacing="0" border="0" id="myTable" width="700px"> 
<tr> 
<td colspan="3"><a href="qualitysystem_complete_details.php?quality_name='. urlencode($row[3]) .'">'. $row[3] .'</a></td>
</tr> 
<tr> 
<td class="heading" colspan="4"><strong>Support activities provided</strong></td> 
</tr>';
$sql2 = "SELECT supportactivity.supportactivity_id AS supportactivity_id, 
supportactivity.supportactivity AS supportactivity, qualityadmin.quality_name AS quality_name, 
qualityadmin.qualityadmin_id AS qualityadmin_id 
FROM supportactivity 
LEFT JOIN supportactivityrelations ON supportactivity.supportactivity_id = supportactivityrelations.supportactivity_id 
LEFT JOIN qualityadmin ON supportactivityrelations.quality_name = qualityadmin.quality_name 
WHERE qualityadmin.quality_name = '$name1' ORDER BY supportactivity"; 

$results2 = mysql_query($sql2); 
while ($row = mysql_fetch_array ($results2)){ 
$supportactivity = $row["supportactivity"]; 
echo '<tr> 
<td colspan="3">' . $row['supportactivity'] . '</td>
</tr>';

}
}
?>   

How can I adjust this so that I am showing each quality_name only once?
I understand that this seems silly because as it stands if a quality_name has one or more supportactivities then it will always query & show multiple instances but I cannot see where my database design is flawed as I have had it looked at on this forum before and it was approved.

[leatherback]

??

I am not sure what you are asking:

As you pointed out: if a quality_name has one or more supportactivities then it will always show multiple instances. If you will never have this, you should have placed them in one table. If you want just one instance, you could use the DISTINCT keyword in your sql query (See the mysql.org website, select syntax for your version of MySQL).

Your database design is -in my view- a bit icky...
You need to use:
supportactivity.supportactivity_id AS supportactivity_id
all the time, as you seem to have the same colum names in multiple tables. If you avoid that (e.g., use supportactivity_id in the table supportactivity, where it is an identifier, and thus unique. In the other tables it is a foreign key, and you could use supportactivity_nr. This would reduce your query and make it easier to work with.
Also.. you use very long colum names, adding to typos and poor legibility.
My 2 cts ;-)

[Tezread]

I think I have answered my own question here. Wise words though Leatherback.
I think one possible solution is to reverse the query so I show the supportactivity with a list of qualitynames under it as oppose to the quality_name with a list of support activities under it.

[leatherback]

There is a multitude of ways to display this sort of information.

You could even keep track of which quality_name you are currently viewing (e.g., using if($current<>$row[quality_name]){echo $row[quality_name]; $current=quality_name;}). It all depends on the information you are trying to provide the user with.

J.

[Roger_Ramjet]

I really must disagree with you over the column names Leatherback. I have for 20 years used the same name for PK:FK pairs just exactly to indicate that they are a pair. This also has the usefull side effect of allowing you to use the simplified join syntax USING(col)

'SELECT * FROM table1 JOIN table2 USING(table1_id)'

In mysql this join syntax also means that the id column is only returned once even if you use SELECT *

Other dbs require that columns are always specified with table.column and also return the results as table.column - it is mysql that is the exception here but then mysql always has had loose, or sloppy, syntax.

[Sxooter]

I do something similar, Roger, but I do it like:

table1 (id serial primary key)
table2 (id serial primary key, table1_id int refererences table1(id))

kind of thing. Also, long table names are real big issue as long as you're using an IDE or something that remembers them for you. I generally just alias them anyway in the SQL. But having a big field name often helps me 6 months to 6 years later remember what they were for... 🙂

[Roger_Ramjet]

Trouble with having every table with a column named 'id' is that it is easy to get confused which table id you are dealing with. As to long names - I agree, make names as meaningfull as possible (which 'id' is not of course). Yiou know as well as I do that the lazy f**ks who insist on saving a few keystrokes end up making more errors and more work for themselves and others down the track.