• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

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.
0
jat465
Asked:
jat465
2 Solutions
 
strickddCommented:
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.
0
 
Neil RussellTechnical Development LeadCommented:
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.
0
 
sammySeltzerCommented:
Depending on how your table is designed, something like this should work:

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

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jat465Author Commented:
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
0
 
strickddCommented:
If you need it working now, remove all spaces when you do the joins. That means it will only matter if the team names are spelled wrong:

SELECT * FROM Games
WHERE NOT EXISTS
((SELECT REPLACE(teamname, ' ', '') as teamname FROM Teams WHERE teamname = Games.visitingteam)
OR
(SELECT REPLACE(teamname, ' ', '') as teamname FROM Teams WHERE teamname = Games.hometeam))

0
 
yesthatbobCommented:
Something's not clicking here. You say "I would like to select records in Games where visitingteam and hometeam are not in games." Are you looking for games where one of the teams is blank? If that's what your looking for, you can use this query:
select * 
from Games
where rtrim(HomeTeam) = "" or HomeTeam is null
	or rtrim(VisitingTeam) = "" or VisitingTeamis null

Open in new window


But your query seems to indicate otherwise. If instead you want to find Games where one or both teams don't exist in the Teams table, use this query:
select rtrim(HomeTeam) as TeamName 
from Teams
where rtrim(HomeTeam) not in (select rtrim(TeamName from Teams)

union

select rtrim(VisitingTeam) as TeamName 
from Games
where rtrim(VisitingTeam) not in (select rtrim(TeamName from Teams)
	and rtrim(TeamName) not in (select rtrim(VisitingTeam) from Games)

Open in new window


If instead you want to find Teams that don't currently have a scheduuled game, use this query:
select TeamName 
from Teams
where rtrim(TeamName) not in (select rtrim(HomeTeam from Games)
	and rtrim(TeamName) not in (select rtrim(VisitingTeam) from Games)

Open in new window

0
 
jat465Author Commented:
These are exactly what I needed, thank you all.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now