Syntax error with inner join

[shauns1]

Hi

Can anyone tell me why the Query below receives the following error?


Syntax Error (Missing Operator) in query expression 'e ON e.ThreadID=t.ThreadID
INNER JOIN tblUsers AS u ON u.UserID=t.CreatorID'

The query is:

SELECT t.ThreadID,t.Thread, t.CreationDate, count(e.EntryID),u.Username
FROM spPFApprovedThreads AS t
INNER JOIN spPFApprovedEntries AS e ON e.ThreadID=t.ThreadID
INNER JOIN tblUsers as u ON u.UserID = t.CreatorID
GROUP BY t.ThreadID,t.Thread,t.CreationDate, u.Username
ORDER BY t.ThreadID;


Many thanks

Shaun

 

[SQLDenis]

Is spPFApprovedEntries a table or a stored proc?



“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[shauns1]

Its a stored procedure...

 

[SQLDenis]

you can't do that
use a UDF that returns a table instead



“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[shauns1]

Hmm

I have the same data in tables (it's just not filtered), tried the same query and got the same error.:

SELECT t.ThreadID,t.Thread, t.CreationDate, count(e.EntryID),u.Username
FROM tblPFThreads AS t
INNER JOIN tblPFEntries AS e ON e.ThreadID=t.ThreadID
INNER JOIN tblUsers as u ON u.UserID = t.CreatorID
GROUP BY t.ThreadID,t.Thread,t.CreationDate, u.Username
ORDER BY t.ThreadID;

Whats a UDF - sorry, new to this.

 

[SQLDenis]

UDF = User defined Function can return a value or a table

You can filter in the join by using WHERE and AND

SELECT t.ThreadID,t.Thread, t.CreationDate, count(e.EntryID),u.Username
FROM tblPFThreads AS t
INNER JOIN tblPFEntries AS e ON e.ThreadID=t.ThreadID
INNER JOIN tblUsers as u ON u.UserID = t.CreatorID
WHERE u.UserID =1
GROUP BY t.ThreadID,t.Thread,t.CreationDate, u.Username
ORDER BY t.ThreadID

I see no problem with this SQL, are you running it from Query Analyzer??


“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[shauns1]

Actually I'm running this in Access, but as far as I can see, that should make no difference. This is all generic SQL stuff isn't it?

 

[SQLDenis]

How are you running stored procedures in Access are you connecting to SQL Server?

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[shauns1]

No, Sorry

I just realised this is a SQL Server programming forum, not SQL Programming.

I'm using Access with ASP.Net rather than SQLServer.

This issue is purely with an Access 'Query' - the cheap mans SQL Server Stored Proc!

Does this shed any more light?

Failing that, do you know any SQL specific forums where I should post. Google seems to be full of SQL Server

 

[SQLDenis]

try this one

http://www.tek-tips.com/threadminder.cfm?pid=181

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[SQLDenis]

This is the one you want

http://www.tek-tips.com/threadminder.cfm?pid=701

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[shauns1]

Hi

I resolved the issue with the following:

SELECT t.ThreadID, t.Thread, t.CreationDate, t.TopicID, count(e.EntryID) AS EntryCount, u.Username
FROM (tblPFThreads AS t INNER JOIN tblPFEntries AS e ON e.ThreadID=t.ThreadID) INNER JOIN tblUsers AS u ON u.Username=t.CreatorID
WHERE t.TopicID=[@TopicID]
GROUP BY t.ThreadID, t.Thread, t.TopicID, t.CreationDate, u.Username
ORDER BY t.ThreadID;


Notice the parenthesis on the first INNER JOIN. I'm no SQL Expert so I can't say why it worked this way and not the other.

Shaun