Query and joins

Tezread

So im having a little trouble understanding how to deal with this query
I have two tables:

qualityadmin:
qualityadmin_id (auto_increment - this is my primary key)
authority_body
address1
address2
etc

and

activity_participant

activity_participant_id
refno
qualityadmin_id
activity

what is supposed to happen is that the participant fills in a form that populates part of the qualityadmin table. The participant can choose one or activities from a list box. These choices are recorded in activity_participant table. My question is how can I run a query that shows all authority_body's that have an activity of X (e.g. Government & statutory) Does this make sense? -
[

etully

First off, I don't understand why the activity_participant table has two id fields. I'll assume that qualityadmin_id is the same in both tables.

select qualityadmin.authority_body from qualityadmin,activity_participant where activity_participant.qualityadmin_id = qualityadmin.qualityadmin_id and activity_participant.activity=X;

This will return a list of authority_body's that chose activity X.

This page explains it well: http://www.w3schools.com/sql/sql_join.asp

vallette

To do a true JOIN do the following:

SELECT qa.authority_body FROM activity_participant ap
INNER JOIN qualityadmin qa ON qa.qualityadmin_id=ap.qualityadmin_id
WHERE ap.activity_participant_id=X

Tezread

I don't get this- ive incorporated your suggestion but I get no returns. My activity_participant table is definitely populated. To clarify- Im looking to show all authority_body's in qualityadmin that have their qualityadmin_id listed in the activity_participant table (i.e one authority_body can have many activities). Here is the activity_participant table in phpmyadmin to demonstrate:

www.mindseyemidlands.co.uk/pic.htm

The' X' I used in the previous thread refers to an activity in this case 'Making Choices'- this is my code which at present returns

Resource id #4=result

 <?php
include("connect.php");

$sql = "select qualityadmin.authority_body from qualityadmin,activity_participant where activity_participant.qualityadmin_id = qualityadmin.qualityadmin_id and activity_participant.activity='Making Choices'";

$result = mysql_query($sql) or die(mysql_error());  

echo $result."=result<br>
";  

while($row=mysql_fetch_array($result))  

{ // NOTE this one ABOVE the echo 
//echo "result found!"; 
//echo $row[0]; 

echo '<a href="infolinks_details.php?authority_body='. urlencode($row[0]) .'">'. $row[0] .'</a><br><br>';  

echo $row[1] .'<br><br>';  

} 
?>

Tezread

Read the tute you mentioned and it looks like ive followed the protocols for normalisation- hows this?

<?php
include("connect.php");

$sql = "SELECT qualityadmin.authority_body, activity_participant.activity
FROM qualityadmin, activity_participant
WHERE qualityadmin.qualityadmin_id = activity_participant.qualityadmin_id AND activity_participant.activity ='Making Choices'";

$result = mysql_query($sql) or die(mysql_error());  

echo $result."=result<br>
";  

while($row=mysql_fetch_array($result))  

{ // NOTE this one ABOVE the echo 
//echo "result found!"; 
//echo $row[0]; 

echo '<a href="infolinks_details.php?authority_body='. urlencode($row[0]) .'">'. $row[0] .'</a><br><br>';  

echo $row[1] .'<br><br>';  

} 
?>

im getting results but need more testing- i need to be able to specify an activity as well as perform thisd queyr above- i tried AND activity_participant.activity ='Making Choices' but no results

Tezread

aha!! ive been testing activitys that do not null values for qualityadmin_id!! is is working now I think..