Compare two tables show records not listed... 1

[Nilgni]

I have a two tables:
Table 1 is a list of teams: Team_Listing
Table 2 is a list of people who have acknoledged a message: Read_Reciepts.

For arguments sake the first only contains team names and nothing else. The second has a the article id, the userid and their teamname.

So far I am able to show who DID reply, but I want to show the teams who have not acknowleged the message.

So if I were speaking my question it would be:
"Show me all the teams from the team table who do not have a record in the reciept table using article number 123 to determine which teams have responded so far"

I remember that MS Access has a 'not listed' wizard query, but I never paid that much attention to how it did it!

Any suggestions?
Thanks Keith

 

[feherke]

Hi

[b]select[/b]
[teal]*[/teal]

[b]from[/b] Team_Listing t
[b]left[/b] [b]join[/b] Read_Reciepts r [b]using[/b] [teal]([/teal]teamname[teal])[/teal]

[b]where[/b] r[teal].[/teal]teamname [b]is[/b] [b]null[/b]
[b]and[/b] t[teal].[/teal]articleid[teal]=[/teal][purple]123[/purple]

Feherke.

 

[Nilgni]

Yes, very great example of what I need. Thanks!

 

[Nilgni]

For those following in my footsteps..
The two actual table names are..
teams
special_message_read_receipts

The goal was to show the teams who did not acknowledge the special message posted. Teams could view the message and click a link to say they read it.

The next page confirmed their read receipt and displayed those who read the message so far AND now.. thanks to this.. those teams that haven't responded.

Here is the SQL based on the above suggestion:

$id_Article = $_GET['id_Article'];

"SELECT teams.Team_Name FROM teams LEFT JOIN special_message_read_receipts ON teams.Team_Name = special_message_read_receipts.Team_Affiliation
WHERE special_message_read_receipts.Team_Affiliation Is Null AND special_message_read_receipts.id_Article = '$id_Article'";


The results now display the teams NOT listed as having read the message.
Here is the full code:

//search out teams not listed yet
mysql_select_db($database_Name, $ConnectionName);

$rsTeamNotListed = "SELECT teams.Team_Name FROM teams LEFT JOIN special_message_read_receipts ON teams.Team_Name = special_message_read_receipts.Team_Affiliation
WHERE special_message_read_receipts.Team_Affiliation Is Null AND special_message_read_receipts.id_Article = '$id_Article'";
$Result_TeamsNotListed = mysql_query($rsTeamNotListed, $ConnectionName) or die(mysql_error());

//list records found

<?php
while($row = mysql_fetch_array($Result_TeamsNotListed))
{
echo $row['Team_Name'];
echo "<br />";
}
?>


?>

 

[feherke]

Hi

3 words : SQL injection vulnerability.

Protect your database with [tt]mysql_real_escape_string()[/tt] :

[navy]$id_Article[/navy] [teal]=[/teal] [highlight][COLOR=darkgoldenrod]mysql_real_escape_string[/color][teal]([/teal][/highlight][navy]$_GET[/navy][teal][[/teal][green][i]'id_Article'[/i][/green][teal]][/teal][highlight][teal])[/teal][/highlight][teal];[/teal]

Feherke.

http://feherke.github.com/

 

[Nilgni]

Thanks for that. Not using this as excuse for ignorance but I am self-taught and these are things that come with learning in a real enviroment or having someone to ask.

If I can indulge you once more on THAT topic.
Is this correct: Session and Post are okay, but GET isn't.

Lightbulb moment: Now I understand that code that Dreamweaver uses above the statement and why they go through the trouble... another piece of the puzzle filled in.

 

[feherke]

Hi

Is this correct: Session and Post are okay, but GET isn't.

Generally anything coming from the client side can be compromised. POST data too. Cookies too. Session can be considered safe, if it gets compromised you will have much bigger problems anyway.


Feherke.

http://feherke.github.com/