JOIN Question

I'm not a real wizard with SQL but am working with it more lately. I've only
recently experimented with JOINS, and am having a problem with the following
.
It says there's an issue "near the JOIN".
Select HoldXref.HoldXrefId, HoldXref.FolderName,
HoldXref.ImageRecId,HoldXref.HoldListId, HoldList.HoldId,
HoldList.HoldType,HoldList.Comment, HoldList.UserName,
HoldTypes.HoldTypeId,HoldTypes.HoldName, HoldTypes.HoldDescription,
HoldTypes.DateBased From HoldXref Where HoldXref.FolderName='Dept_06' And
ImageRecId = 11 INNER JOIN HoldList On HoldXref.HoldListId = HoldList.HoldId
INNER JOIN HoldTypes On HoldList.HoldType = HoldTypes.HoldTypeId
This seems really complicated. When I experimented with it originally, I
didn't have the "where HoldXref.FolderName='Dept_06' and ImageRecId = 11"
It was just a prototype and it worked. However, I want to limit the output
to this criteria and am not sure how to do that. Maybe it's as simple as
putting parenthesis around them, but I've tried several things and it's not
working. And for one reason or another, the stuff I find in the book(s) don'
t
happen to have an example similar to what I'm trying to do.You have your WHERE clause embedded before your JOINs... Please work on your
formatting a bit. It will help you resolve problems like these much more
easily.
SELECT
HoldXref.HoldXrefId,
HoldXref.FolderName,
HoldXref.ImageRecId,
HoldXref.HoldListId,
HoldList.HoldId,
HoldList.HoldType,
HoldList.Comment,
HoldList.UserName,
HoldTypes.HoldTypeId,
HoldTypes.HoldName,
HoldTypes.HoldDescription,
HoldTypes.DateBased
FROM HoldXref
INNER JOIN HoldList On HoldXref.HoldListId = HoldList.HoldId
INNER JOIN HoldTypes On HoldList.HoldType = HoldTypes.HoldTypeId
WHERE
HoldXref.FolderName='Dept_06'
And ImageRecId = 11
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:96A94732-929E-4756-BB3E-E60ABC720D56@.microsoft.com...
> I'm not a real wizard with SQL but am working with it more lately. I've
only
> recently experimented with JOINS, and am having a problem with the
following.
> It says there's an issue "near the JOIN".
> Select HoldXref.HoldXrefId, HoldXref.FolderName,
> HoldXref.ImageRecId,HoldXref.HoldListId, HoldList.HoldId,
> HoldList.HoldType,HoldList.Comment, HoldList.UserName,
> HoldTypes.HoldTypeId,HoldTypes.HoldName, HoldTypes.HoldDescription,
> HoldTypes.DateBased From HoldXref Where HoldXref.FolderName='Dept_06' And
> ImageRecId = 11 INNER JOIN HoldList On HoldXref.HoldListId =
HoldList.HoldId
> INNER JOIN HoldTypes On HoldList.HoldType = HoldTypes.HoldTypeId
>
> This seems really complicated. When I experimented with it originally, I
> didn't have the "where HoldXref.FolderName='Dept_06' and ImageRecId = 11"
> It was just a prototype and it worked. However, I want to limit the output
> to this criteria and am not sure how to do that. Maybe it's as simple as
> putting parenthesis around them, but I've tried several things and it's
not
> working. And for one reason or another, the stuff I find in the book(s)
don't
> happen to have an example similar to what I'm trying to do.|||>> It says there's an issue "near the JOIN".
Why do you have the INNER JOIN clause after the WHERE clause? Did you check
the exact syntax for a SELECT statement in SQL Server Books Online and
looked at the examples given there?
Anith