Solved

Checking records with spaces

Posted on 2011-09-06
7
197 Views
Last Modified: 2012-08-13
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
Comment
Question by:jat465
7 Comments
 
LVL 28

Expert Comment

by:strickdd
Comment Utility
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
 
LVL 37

Expert Comment

by:Neil Russell
Comment Utility
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
 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility
Depending on how your table is designed, something like this should work:

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

0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:jat465
Comment Utility
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
 
LVL 28

Accepted Solution

by:
strickdd earned 250 total points
Comment Utility
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
 
LVL 4

Assisted Solution

by:yesthatbob
yesthatbob earned 250 total points
Comment Utility
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
 

Author Closing Comment

by:jat465
Comment Utility
These are exactly what I needed, thank you all.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now