How come this JOIN no work?

SELECT c.Custid, nr.TextMessageLNEs, nr.CellPhoneCarrierId,
nr.CellMailAlias,
c.CustFullNameDisp, cp.phonenumber AS phone
FROM customers c
JOIN customers_notreplicated nr
ON c.custid=nr.custid
WHERE c.custid=16
JOIN custphone cp
ON c.custid = cp.custid
WHERE cp.phonetypeid = 11
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'JOIN'. (the second JOIN is having
the problem)
I suspect it's the WHERE clause - but if you can't have a WHERE clause
before the second join, this seems pretty lame to me... but what do i
knowThe problem is the WHERE clause among the JOINs. Only one WHERE
clause is allowed (unless in a subquery, of course), and it has to
appear after the FROM clause and all its parts.
SELECT c.Custid, nr.TextMessageLNEs, nr.CellPhoneCarrierId,
nr.CellMailAlias,
c.CustFullNameDisp, cp.phonenumber AS phone
FROM customers c
JOIN customers_notreplicated nr
ON c.custid=nr.custid
JOIN custphone cp
ON c.custid = cp.custid
WHERE c.custid=16
AND cp.phonetypeid = 11
Roy Harvey
Beacon Falls, CT
On 17 Jan 2007 16:05:31 -0800, tootsuite@.gmail.com wrote:

>SELECT c.Custid, nr.TextMessageLNEs, nr.CellPhoneCarrierId,
>nr.CellMailAlias,
> c.CustFullNameDisp, cp.phonenumber AS phone
> FROM customers c
> JOIN customers_notreplicated nr
> ON c.custid=nr.custid
> WHERE c.custid=16
> JOIN custphone cp
> ON c.custid = cp.custid
> WHERE cp.phonetypeid = 11
>Msg 156, Level 15, State 1, Line 7
>Incorrect syntax near the keyword 'JOIN'. (the second JOIN is having
>the problem)
>I suspect it's the WHERE clause - but if you can't have a WHERE clause
>before the second join, this seems pretty lame to me... but what do i
>know|||The WHERE clause is out of place (and there are too many of them). I
think you probably want something like this:
SELECT
c.Custid,
nr.TextMessageLNEs,
nr.CellPhoneCarrierId,
nr.CellMailAlias,
c.CustFullNameDisp,
cp.phonenumber AS phone
FROM customers AS c
INNER JOIN customers_notreplicated AS nr ON c.custid = nr.custid
INNER JOIN custphone AS cp ON c.custid = cp.custid
WHERE c.custid = 16
AND cp.phonetypeid = 11
Also, which database user(s) owns the [customers], [customers_notrep
licated] and [custphone] tables? You ought to explicitly state the obje
ct owners - it avoids ambiguity and adds a small performance increase due to
the way SQL Server resolves object nam
es.
Check out the Transact-SQL SELECT syntax in SQL Books Online:
http://msdn2.microsoft.com/en-us/library/ms189499.aspx
*mike hodgson*
http://sqlnerd.blogspot.com
tootsuite@.gmail.com wrote:

>SELECT c.Custid, nr.TextMessageLNEs, nr.CellPhoneCarrierId,
>nr.CellMailAlias,
> c.CustFullNameDisp, cp.phonenumber AS phone
> FROM customers c
> JOIN customers_notreplicated nr
> ON c.custid=nr.custid
> WHERE c.custid=16
> JOIN custphone cp
> ON c.custid = cp.custid
> WHERE cp.phonetypeid = 11
>Msg 156, Level 15, State 1, Line 7
>Incorrect syntax near the keyword 'JOIN'. (the second JOIN is having
>the problem)
>I suspect it's the WHERE clause - but if you can't have a WHERE clause
>before the second join, this seems pretty lame to me... but what do i
>know
>
>