sql script

[Arjan80]

I have to made a result of soccerteams who does NEVER played on field 1

There are 3 tables that can be used for this one (the connections)
table 1 named: team table 2 named: game table 3 named: home
t_code g_code g_code
teamname t_code fieldnr
class opponent
trainer date
leader arbiter

I have wrote this:
select distinct te.t_code, te.teamname
from team te join game g on te.t_code = g.t_code and
g_code not in (select h.g_code from thuis h where h.fieldnr = '1');

If I execute this sql I get 12 teams (it has to be 8), the reason I know:
if a team 1 time not played on field 1, so on field 2 for example it will be printed on the screen, but the possibility is that the same team has ever played on field 1.

Can anyone help? The use of Except and Minus is not allowed.

thnx,
Arjan

 

[rac2]

This query has all of the teams that played on field 1. These are the ones you don't want.

SELECT DISTINCT t_code
  FROM game
  JOIN home ON game.g_code = home.g_code
WHERE home.fieldnr = 1

So, this query has the teams that never played on field 1.

SELECT t_code FROM team
WHERE t_code NOT IN (
   SELECT DISTINCT t_code
     FROM game
     JOIN home ON game.g_code = home.g_code
   WHERE home.fieldnr = 1
)

maybe.

 

[Arjan80]

Thanks!!! You are wonderfull!!!

It was quiet easy when I saw that script. Sometimes I got a headache of those sql scripts, lol...

Arjan