INNER JOIN problem

[danb]

Hi, further to my troubles today, I'm having problems with a JOIN statement.

I have three tables:

tblUniversity:
UniversityId (Primary Key)
Name

tblQualification
QualificationId (Primary Key)
Type

tblUniversityQualification:
Id (Primary Key)
UniversityId (Foreign Key)
QualificationId (Foreign Key)


I belive this is the correct format for a many-to-many relationship.

Anyhow, I'm now trying to select all Qualification Types for a particular University.

My SQL statement is running in a web application and is as follows:

SELECT tblQualification.Type FROM tblQualification INNER JOIN tblQualification.Id ON tblUniversityQualification.QualificationId WHERE tblUniversityQualification = 4

(where '4' is the UniversityId of a particular university), but I just get errors on this line.

Any ideas as to how to do this would be much appreciated...

 

[Newman]

First,

WHERE tblUniversityQualification = 4

should read:

WHERE tblUniversityQualification[B].UniversityId [/B] = 4

Also, make sure that "UniversityId" is numeric. Else, it should read:

WHERE tblUniversityQualification.UniversityId [B]= '4'[/B]

 

[danb]

Thanks very much, this is working now...

 

[ecniv]

SELECT tblQualification.Type
FROM tblQualification INNER JOIN tblQualification.Id ON tblUniversityQualification.QualificationId 
WHERE tblUniversityQualification = 4

To me that statement looks crap.

Glad you managed to get it working though.

Select table.field, etc
From table1 left/inner join table2 on table1.field=table2.field
Where table.field=<value>

Not even sure how you got the first sql statement to work right, I guess access is forgiving... or thinks it knows what you meant


Vince

 

[danb]

Thanks. How does it look "crap". It follows the exact format of:

SELECT field FROM table1 INNER JOIN table2 ON table1.PK = table2.PK

And it works perfectly.

 

[ecniv]

SELECT tblQualification.Type 
FROM tblQualification INNER JOIN tblQualification.Id ON tblUniversityQualification.QualificationId 
WHERE tblUniversityQualification = 4

I'm refering to the initial post...
As pointed out by newman

SELECT tblQualification.Type 
FROM tblQualification INNER JOIN [COLOR=Olive]tblUniversityQualification[/color]
ON [COLOR=SandyBrown]tblQualification.Id=tblUniversityQualification.QualificationId[/COLOR]
WHERE tblUniversityQualification[COLOR=YellowGreen].field[/color] = 4

The first colour is the second table name (note that you didn't specify it in the quoted statement (top))
The second colour is the join between the two tables
The third colour is the missing field, which you put in and apparently the statement ran...


Vince