?
Solved

Checking records with spaces

Posted on 2011-09-06
7
Medium Priority
?
240 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 28

Expert Comment

by:strickdd
ID: 36490300
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
ID: 36490313
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 29

Expert Comment

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

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

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:jat465
ID: 36490431
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 1000 total points
ID: 36490460
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 1000 total points
ID: 36490474
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
ID: 36490651
These are exactly what I needed, thank you all.
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

764 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