Solved

Checking records with spaces

Posted on 2011-09-06
7
231 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

726 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