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
know
The 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

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|||This is a multi-part message in MIME format.
--030107000002010507010904
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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_notreplicated] and [custphone] tables? You ought to explicitly state the object owners - it avoids ambiguity and adds a small performance increase due to the way SQL Server resolves object names.
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
>
>
--030107000002010507010904
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>The WHERE clause is out of place (and there are too many of them).
I think you probably want something like this:<br>
</tt>
<pre wrap="">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_notreplicated] and [custphone] tables? You ought to explicitly state the object owners - it avoids ambiguity and adds a small performance increase due to the way SQL Server resolves object names.
Check out the Transact-SQL SELECT syntax in SQL Books Online:
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn2.microsoft.com/en-us/library/ms189499.aspx</a>">http://msdn2.microsoft.com/en-us/library/ms189499.aspx">http://msdn2.microsoft.com/en-us/library/ms189499.aspx</a>
</pre>
<div class="moz-signature">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:tootsuite@.gmail.com">tootsuite@.gmail.com</a> wrote:
<blockquote
cite="mid1169078731.184826.289500@.s34g2000cwa.googlegroups.com"
type="cite">
<pre wrap="">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
</pre>
</blockquote>
</body>
</html>
--030107000002010507010904--

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
>
>