join 2 text fields

Question

join 2 text fields

emailcooney

SSC Veteran

Points: 219
More actions
August 25, 2010 at 9:21 am

#72935

i have made a db contain these table with these values
CREATE TABLE tblCompeditors (CompID AutoIncrement,MemberID TEXT(100),FirstName text(100),LastName TEXT(100),Address1 TEXT(100),Address2 TEXT(100),Town TEXT(100),County TEXT(100),PostCode Text(15),Phone Byte,Email TEXT(100),Sex TEXT(10),DOB Date,Club TEXT(100))
CREATE TABLE tblRaceEnterants (CompID LONG,RaceNumber Integer,RaceID TEXT(100),LateEntry TEXT)
CREATE TABLE tblRaceEvents (RaceID TEXT(100),RaceLocation Text,Discription text, DateOn Date)
i have to join all 3 together but havin a problem on the join statment i think i'm missing something
SELECT tblResults.FinishPosition,tblRaceEnterants.RaceNumber,tblResults.FinishTime, tblResults.Age FROM tblResults
INNER JOIN tblRaceEnterants ON tblResults.RaceID = tblRaceEnterants.RaceID INNER JOIN tblCompeditors ON tblResults.CompID = tblCompeditors.CompID
WHERE (tblResults.RaceID = 'FELIXSTOWE-5_KM-25/08/2010');
i can get either with just one join but i need to join both to get the info i need
any help would be great cheers cooney txtPost_CommentEmoticon(':-D');

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

emailcooney (8/25/2010)
i have made a db contain these table with these values
CREATE TABLE tblCompeditors (CompID AutoIncrement,MemberID TEXT(100),FirstName text(100),LastName TEXT(100),Address1 TEXT(100),Address2 TEXT(100),Town TEXT(100),County TEXT(100),PostCode Text(15),Phone Byte,Email TEXT(100),Sex TEXT(10),DOB Date,Club TEXT(100))
CREATE TABLE tblRaceEnterants (CompID LONG,RaceNumber Integer,RaceID TEXT(100),LateEntry TEXT)
CREATE TABLE tblRaceEvents (RaceID TEXT(100),RaceLocation Text,Discription text, DateOn Date)
i have to join all 3 together but havin a problem on the join statment i think i'm missing something
SELECT tblResults.FinishPosition,tblRaceEnterants.RaceNumber,tblResults.FinishTime, tblResults.Age FROM tblResults
INNER JOIN tblRaceEnterants ON tblResults.RaceID = tblRaceEnterants.RaceID INNER JOIN tblCompeditors ON tblResults.CompID = tblCompeditors.CompID
WHERE (tblResults.RaceID = 'FELIXSTOWE-5_KM-25/08/2010');
i can get either with just one join but i need to join both to get the info i need
any help would be great cheers cooney txtPost_CommentEmoticon(':-D');

Well, your query references a "tblResults", which you didn't provide any DDL for. Could it be that you're joining to the wrong table?

You might want to read the first link in my signature, then post some (fake) sample data that shows the problem, and what the expected results should be based on that sample data.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

emailcooney SSC Veteran Points: 219 More actions · Answer 2

Cheers for the reply just got it kind of working by putting in brackets

SELECT tblResults.FinishPosition,tblRaceEnterants.RaceNumber,tblResults.FinishTime, tblResults.Age FROM ((tblResults

INNER JOIN tblRaceEnterants ON tblResults.RaceID = tblRaceEnterants.RaceID) INNER JOIN tblCompeditors ON tblResults.CompID = tblCompeditors.CompID)

WHERE (tblResults.RaceID = 'FELIXSTOWE-5_KM-25/08/2010');

but i now seem to get an extra column ???????http://www.sqlservercentral.com/Forums/Skins%5CClassic%5CImages/MessageIcons/Whistling.gif