asked on

Checking records with spaces

Hello,
I am working with Microsoft SQL 2005. I have 2 tables, Games and Teams. Games has 2 columns visitingteam and hometeam. Teams has one column named teamname. I would like to select records in Games where visitingteam and hometeam are not in games. I have tried this:
SELECT * FROM Games
WHERE NOT EXISTS
((SELECT teamname FROM Teams WHERE teamname = Games.visitingteam)
OR
(SELECT teamname FROM Teams WHERE teamname = Games.hometeam))

I think the issue may be with extra spaces in the tables. I tried RTRIM(LTRIM()) as well as several different uses of wildcards, but I could not get anything to work. When I did get a result from the query it returned the entire games table. I am not sure how to proceed.

strickdd

First, i would check you data types and see what data is actually in each table in each row.

Personally, I would restructure the tables more like this:

Games
GameId int NOT NULL (PrimaryKey)
HomeTeamId int NOT NULL (FK to Teams table)
VisitingTeamId int NOT NULL (FK to Teams table)

Teams
TeamId int NOT NULL (Primary Key)
TeamName varchar(200) NOT NULL

This will make joins easier, more efficient, and enforce data integrity.

Neil Russell

Your problem is one of database design. Its not uncommon.

You tables should be...
Teams: 2 fields -> TeamID, TeamName
Games: 3 fields -> GameID, VisingteamID, HomeTeamID

Then you only ever enter TEXT in the Teams table and store the TeamID in the games table.

sammySeltzer

Depending on how your table is designed, something like this should work:

SELECT * FROM GAMES
WHERE TeamID NOT IN
(SELECT TeamID FROM TEAM )

jat465

ASKER

I will work on the redesign of my DB, but I really need to make this work quickly before I do that. I will attempt sammy's comment later today.

Thanks all

ASKER CERTIFIED SOLUTION

strickdd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

yesthatbob