Jump to content


Photo

LEFT JOIN Query help needed


  • Please log in to reply
3 replies to this topic

#1 Woodie

Woodie
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 31 August 2003 - 12:35 AM

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!

#2 Dissonance

Dissonance
  • Members
  • PipPip
  • Member
  • 12 posts
  • LocationChicago, IL

Posted 31 August 2003 - 12:59 AM

[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.
Friends don't let friends vote Republican.

#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 31 August 2003 - 08:46 AM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 Woodie

Woodie
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 01 September 2003 - 02:49 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users