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
Solved

Checking records with spaces

Posted on 2011-09-06
7
226 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
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 28

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 250 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 250 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

828 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