LEFT JOIN Query help needed

[Woodie]

Hope soembody can help me!

 

I have two tables. One has the names of teams and a primary key identifying the team. The other table has list of fixtures using the team IDs and time stamps of the fixtures.

 

TableTeams

teamID TeamName

 

TableFixtures

fixID HomeTeamID awayTeamID timStam

 

I want a query that will list all the teamnames given in TeamName that DO NOT appear in the Fixtures table when timStam is between two specified values!

 

Please Help I have tried several times with the only succes not getting an error!

[Dissonance]

[php:1:3b4f32b051]

$sql = \"SELECT TeamName FROM TableTeams WHERE TeamName.teamID != TableFixtures.HomeTeamID AND TeamName.teamID != awayTeamID AND TableFixtures.timStam > [value1] AND TableFixtures.timStam < [value2]\"

[/php:1:3b4f32b051]

 

Value 1 and Value 2 will be your two limiting values, obviously.

 

In all honesty, though, I think you\'re better off using two separate queries. This type of join isn\'t very efficient, imo.

[Barand]

To do it without subquries you\'ll need to create a temporary table

 

[php:1:0a0e51b533]<?php

mysql_query( \" create table tmpfix (teamID int, fixID int)\");

mysql_query( \"INSERT INTO tmpfix SELECT HomeTeamID, fixID FROM tablefixtures WHERE timStam

BETWEEN \'$a\' and \'$b\'\");

mysql_query( \"INSERT INTO tmpfix SELECT awayTeamID, fixID FROM tablefixtures WHERE timStam

BETWEEN \'$a\' and \'$b\'\");

$res = mysql_query(\"select teamname from teams t left join tmpfix f

on t.teamID = f.teamID where f.fixID is NULL\");

?>[/php:1:0a0e51b533]

 

hth

[Woodie]

Thanks for the tips I will try it out and see what happens!