Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Cant figure out a query

Author  Topic 

Guran
Starting Member

17 Posts

Posted - 2005-07-26 : 15:06:05
I have been sitting for hours trying to figure out a query that I need. The application handles members for a sports club. There are two tables involved: Members and TeamMembers. Members holds some basic data about a member in the club. TeamMembers links the mebers to teams. A member can be part of several or no teams.
I need a query that can do a search among all members not part of a team. This is what I can figure out for myself:
SELECT Members.* FROM Members, TeamMembers WHERE Members.FirstName LIKE @FirstName AND Members.LastName LIKE @LastName AND Members.PersNr LIKE @PersNr AND Members.BirthYear LIKE @BirthYear ORDER BY Members.LastName ASC


So the sql above needs checks to pick out all members not part of the a specific team (TeamID). The members can be part of other teams or not part of any teams. The problem seems to be when a member is not part of any team. All the tries I have made have always caused the result to be null when this happens. I have tried everything I can think of using IS NULL, IS NOT NULL, ISNULL(), COALESCE() and such, but nothing I have tried has worked.
I would be really happy if anyone can think of a solution to this problem because I cannot.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-26 : 15:46:56
what is null? a specific column?? maybe you need to specify a left join??

any...heres a go

Declare @teamId int
Set @teamId = 2

SELECT
Members.*
FROM Members A
Left Join
(Select * From TeamMembers Where TeamId=@teamId) B
On A.memberId = B.memberId
WHERE A.FirstName LIKE @FirstName
AND A.LastName LIKE @LastName
AND A.PersNr LIKE @PersNr
AND A.BirthYear LIKE @BirthYear
and B.TeamId is null -- ensures not on @teamId
ORDER BY Members.LastName ASC



Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."
Go to Top of Page

emilg
Starting Member

13 Posts

Posted - 2005-07-27 : 02:17:30
Can you put the DDL for the 2 tables so we have a clear idea about the problem?

Tks
Go to Top of Page

Guran
Starting Member

17 Posts

Posted - 2005-07-27 : 07:54:48
Sure:


CREATE TABLE Teams
(
TeamID int PRIMARY KEY IDENTITY(1,1) NOT NULL,
TeamName varchar(64) NOT NULL,
TeamIndex smallint NOT NULL UNIQUE
)

CREATE TABLE Members
(
MemberID int PRIMARY KEY IDENTITY(1,1) NOT NULL,
FirstName varchar(128) NOT NULL,
LastName varchar(128) NOT NULL,
PersNr varchar(10) NOT NULL,
OriginalClub varchar(128),
SeasonNr varchar(4),
BirthYear varchar(4)
)

CREATE TABLE TeamMembers
(
TeamMemberID int PRIMARY KEY IDENTITY(1,1) NOT NULL,
TeamID int NOT NULL REFERENCES Teams(TeamID),
MemberID int NOT NULL REFERENCES Members(MemberID),
TypeID int NOT NULL REFERENCES MemberTypes(TypeID),
MemberImageID int REFERENCES MemberImages(MemberImageID),
SpecificType varchar(128),
Number varchar(4),
Nickname varchar(32),
Presentation text
)


As you can see there are other things in the tables but I didnt think they were interesting in this case.
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-27 : 08:22:47
did you give my example a try?? can you answer the questions I asked?? That would help determine exactly what you are trying to fix.

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."
Go to Top of Page

Guran
Starting Member

17 Posts

Posted - 2005-07-27 : 09:58:35
The tries I made before that caused the result to be null were kind of specific to those tries, but they were all caused by the fact that the result I got from the TeamMembers table was null because there were no members in that table at all. The sql had to be able to handle this situation and pick out all values from the Members table.
Anyway your code seems to work flawlessly.

Many thanks from a developer who now can continue working with other stuff again!
Go to Top of Page

emilg
Starting Member

13 Posts

Posted - 2005-07-27 : 10:11:47
This should give all the Members who does not belong to team @TeamID even if they are not assigned to any team. Hope this is what you wish.

SELECT m.*
FROM Members m
left outer join TeamMembers tm
on m.MemberID = tm.MemberID
WHERE
m.FirstName LIKE @FirstName
AND m.LastName LIKE @LastName
AND m.PersNr LIKE @PersNr
AND m.BirthYear LIKE @BirthYear
and
(tm.TeamID is NULL or
tm.MemberID not in (select MemberID from TeamMembers where TeamID= @TeamID)
) -- all members not part of a team
ORDER BY m.LastName ASC

HTH,
Emil
Go to Top of Page
   

- Advertisement -