SQL Help

[FortySix&2]

I am trying to create a simple sql query with no luck. It involves three tables and I am trying to use a join. It gives the following error:

Syntax error (missing operator) in query expression 'tblLocation.LocationID = tblJob.LocationID INNER JOIN tblJobCategory ON tblJobCategory.JobCategoryID = tblJob.JobCategoryID'.

Here is the sql statement:

SELECT tblJob.JobID, tblJob.JobCategoryID, tblJob.LocationID, tblJob.JobCompany, 
              tblLocation.LocationID, tblLocation.LocationState, tblLocation.LocationName,
              tblJobCategory.JobCategoryID, tblJobCategory.JobCategoryName
FROM tblJob
INNER JOIN tblLocation 
ON tblLocation.LocationID = tblJob.LocationID 
INNER JOIN tblJobCategory 
ON tblJobCategory.JobCategoryID = tblJob.JobCategoryID
WHERE tblJob.JobCompany = 1 OR tblJob.JobCompany = 2;

Granted My sql skills aern't the greatest but it will work with just the first join.

Any help is appreicaited.

 

[ameoba]

I'd probably just write it without and explicit join...

SELECT tblJob.JobID, tblJob.JobCategoryID, tblJob.LocationID, tblJob.JobCompany, 
              tblLocation.LocationID, tblLocation.LocationState, tblLocation.LocationName,
              tblJobCategory.JobCategoryID, tblJobCategory.JobCategoryName
FROM tblJob, tblLocation, tblJobCategory 
WHERE tblJob.JobCompany = 1 OR tblJob.JobCompany = 2
AND tblLocation.LocationID = tblJob.LocationID
AND tblJobCategory.JobCategoryID = tblJob.JobCategoryID

IMHO, that's easier to read anyways.

 

[mikeblas]

Your syntax looks fine to me, FortySix&2. Did you misspell an identifier name?

 

[FortySix&2]

Maybe I have staring at it too long but it looks like all the identifiers are spelled right.

Ameoba if I use the sql that you provided it returns every posible outcome not just the ones that meet the 1 or 2 criteria.

Thanks for the fast replysI am trying to get this down today.

 

[mikeblas]

Ameoba if I use the sql that you provided it returns every posible outcome not just the ones that meet the 1 or 2 criteria.

Yep; Amoeba unintentionally demonstrated one of the reasons the newer syntax is preferable.

Try this, if you want to give the older syntax a whirl:

SELECT tblJob.JobID, tblJob.JobCategoryID, tblJob.LocationID, tblJob.JobCompany, 
              tblLocation.LocationID, tblLocation.LocationState, tblLocation.LocationName,
              tblJobCategory.JobCategoryID, tblJobCategory.JobCategoryName
FROM tblJob, tblLocation, tblJobCategory 
WHERE (tblJob.JobCompany = 1 OR tblJob.JobCompany = 2)
AND tblLocation.LocationID = tblJob.LocationID
AND tblJobCategory.JobCategoryID = tblJob.JobCategoryID

 

[Inalende]

Try this for the joins:

SELECT tblJob.JobID, tblJob.JobCategoryID, tblJob.LocationID, tblJob.JobCompany, 
              tblLocation.LocationID, tblLocation.LocationState, tblLocation.LocationName,
              tblJobCategory.JobCategoryID, tblJobCategory.JobCategoryName
FROM (tblJob INNER JOIN tblLocation ON tblJob.LocationID = tblLocation.LocationID) INNER JOIN tblJobCategory ON tblJob.jobCategoryID = tblJobCategory.JobCategoryID
WHERE tblJob.jobCompany=1 Or tblJob.jobCompany=2;

 

[FortySix&2]

Man thanks alot.

That did the trick. I am not sure why this works, I am not a rookie when it comes to conditional statements but why did the ( ) make such a dif.

 

[Inalende]

I believe it's the syntax for more than 1 join, not sure exactly why but might be something like splitting the joins apart.

 

[mikeblas]

I believe it's the syntax for more than 1 join, not sure exactly why but might be something like splitting the joins apart.

I don't need it when writing multiple joins against SQL Server. What DMBS are you using, FortySix?

 

[ameoba]

Ameoba if I use the sql that you provided it returns every posible outcome not just the ones that meet the 1 or 2 criteria.

Oops. I missed the OR. I'm too used to seeing a single predicate per line.