Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

join 2 text fields Expand / Collapse
Author
Message
Posted Wednesday, August 25, 2010 9:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 26, 2010 3:37 AM
Points: 7, Visits: 25
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('');
Post #974953
Posted Wednesday, August 25, 2010 2:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:58 PM
Points: 5,359, Visits: 8,921
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('');


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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #975176
Posted Wednesday, August 25, 2010 2:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 26, 2010 3:37 AM
Points: 7, Visits: 25
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
Post #975200
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse