3 Table Join

I need to perform a 3 table join but my sql is a little rusty and I was never that good at Joins anyway!

I'll try and explain the basic DB layout. It's for a very simple forum board I'm making, I have 3 tables at the moment, one contains the messages which belong to a topics, which have their own table. And the topics all belong to a table listing all the forums.

tblThread
threadID PK
topicID FK
Author
Message
MessageDate

tblTopic
topicID PK
forumID FK
Subject
numViews

tblForum
forumID PK
forumName
forumDescription
forumOwner

What I'm trying to achieve is to get the number threads belonging to a forum.

So far my attempt at an sql query looks like this:

SELECT COUNT('tblthread.threadID') AS postsCount
FROM tblThread
INNER JOIN tblTopic ON tblThread.topicID = tblTopic.topicID
INNER JOIN tblForum ON tblForum.forumID = tblTopic.forumID
WHERE tblForum.forumID=1

but that's missing an operator somewhere. Anyone know where i'm going wrong?

Thanks,
RichardSELECT COUNT('tblthread.threadID') AS postsCount
There shouldn't be apostrophes around the column name.|||your statement seems be fine. what database server do you use? there might be some syntax problem (INNER JOIN or something) try this one, but basically that's the same:

SELECT COUNT(*) AS postsCount
FROM tblThread, tblTopic, tblForum
WHERE tblThread.topicID = tblTopic.topicID
AND tblForum.forumID = tblTopic.forumID
AND tblForum.forumID=1|||There shouldn't be apostrophes around the column name.
actually this shouldn't be issue. you can put whatever as count() argument. it can be column name, *, or some constant such as 1 or 'XXX'. in this case it's constant string which shouldn't affect result.|||True, I stand corrected.|||Hi Guys,
Thanks for the help. I'm using MS Access as the DB. I tried your suggestion madafaka but I'm still getting the same error:

"Microsoft JET Database Engine (0x80040E14)
Syntax error in FROM clause."

Is this an error specific to access?

Thanks.|||daffy_dowden,
I created all 3 tables in MS Access (I think it's 2000 version)

I created Query (Create Query in Design view) using (SQL View)
simply: pasted code posted before

your code returned exactly the same error as you described. I don't know why, but what you can expect from MS Access :-)
Then I used (Design view) and it generated this code, which works fine:

SELECT count(*)
FROM tblForum
INNER JOIN (tblThread INNER JOIN tblTopic ON tblThread.topicID = tblTopic.topicID) ON tblForum.forumID = tblTopic.forumID
WHERE tblForum.forumID=1;

code I posted before worked fine, so I don't know why you received an error

SELECT COUNT(*) AS postsCount
FROM tblThread, tblTopic, tblForum
WHERE tblThread.topicID = tblTopic.topicID
AND tblForum.forumID = tblTopic.forumID
AND tblForum.forumID=1;|||I guess Access joins tables step by step : tblThread join tblTopic, and then join tblForum

SELECT COUNT('tblthread.threadID') as postsCount
FROM
(tblThread INNER JOIN tblTopic ON tblThread.topicID = tblTopic.topicID)
INNER JOIN tblForum ON tblForum.forumID = tblTopic.forumID
WHERE tblForum.forumID=1

3 Questions

Dear Professionals,
1. Could any one explain in a simple word "what Collation play a role in
SqlServer"
2. I want the restrict all the user to connect only from SA login .. can't
connect through windows authentication.. what I have to do for this ?
3. Which license would be good Per seat or Per processor.
Thanks
NOOR
Noor,
#1. Multi language support.(you asked for a word , okay that was 3:-)
#2: Remove all Windows logins including builtin\administrators and non-sa
sql logins.Juist curious, why do you want to do that?Removing buitin\admin
needs to be done very carefully.
#3. Difficult to say.If one was better than the other why would Microsoft
give you two?Depends on your requirement.See
'How to Buy'
http://www.microsoft.com/sql/howtobuy/default.asp
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Noorali Issani" <naissani@.softhome.net> wrote in message
news:O1Q8UN7QEHA.3140@.tk2msftngp13.phx.gbl...
> Dear Professionals,
> 1. Could any one explain in a simple word "what Collation play a role in
> SqlServer"
> 2. I want the restrict all the user to connect only from SA login .. can't
> connect through windows authentication.. what I have to do for this ?
> 3. Which license would be good Per seat or Per processor.
> Thanks
> NOOR
>
|||Thanks Dinesh..
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:%236xQRa8QEHA.3728@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Noor,
> #1. Multi language support.(you asked for a word , okay that was 3:-)
> #2: Remove all Windows logins including builtin\administrators and non-sa
> sql logins.Juist curious, why do you want to do that?Removing buitin\admin
> needs to be done very carefully.
> #3. Difficult to say.If one was better than the other why would Microsoft
> give you two?Depends on your requirement.See
> 'How to Buy'
> http://www.microsoft.com/sql/howtobuy/default.asp
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Noorali Issani" <naissani@.softhome.net> wrote in message
> news:O1Q8UN7QEHA.3140@.tk2msftngp13.phx.gbl...
can't
>

2005: Database cannot be opened

Hello,
Could you explain me please the following error:

"Database 'DemoDotNET' cannot be opened due to inaccessible files or
insufficient memory or disk space. (Microsoft SQL Server, Error: 945)"

I have checked: free disk space is large enough, I have much enough
memory, other files in the directory are accessible.

Here is tail of ERRORLOG:

2006-06-12 21:19:55.68 Logon Error: 18456, Severity: 14, State:
16.
2006-06-12 21:19:55.68 Logon Login failed for user 'PC\Robert'.
[CLIENT: <local machine>]
2006-06-12 21:20:43.39 Server Server resumed execution after
being idle 1 seconds: user activity awakened the server. This is an
informational message only. No user action is required.

Please help to solve this.
Thank you.
/RAM/R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:
> "Database 'DemoDotNET' cannot be opened due to inaccessible files or
> insufficient memory or disk space. (Microsoft SQL Server, Error: 945)"
> I have checked: free disk space is large enough, I have much enough
> memory, other files in the directory are accessible.

Or there is some file for DemoDotNET which is located somewhere where
you don't think it is.

Unfortunately, with the information at hand it's impossible to say. You
could at least have posted the part of the error log where this message
appears. Or tell us when you get this error. Does it happen at startup?
Do you try to attach the database? Something else?

And, not the least, you did not tell us which version of SQL Server
you are using.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 12 Jun 2006 21:36:15 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:
>> "Database 'DemoDotNET' cannot be opened due to inaccessible files or
>> insufficient memory or disk space. (Microsoft SQL Server, Error: 945)"
>>
>> I have checked: free disk space is large enough, I have much enough
>> memory, other files in the directory are accessible.
>Or there is some file for DemoDotNET which is located somewhere where
>you don't think it is.
>Unfortunately, with the information at hand it's impossible to say. You
>could at least have posted the part of the error log where this message
>appears. Or tell us when you get this error. Does it happen at startup?
>Do you try to attach the database? Something else?
>And, not the least, you did not tell us which version of SQL Server
>you are using.

I am using SQL Server 2005.
In directory of the database I have files: DemoDotNET.mdf,
DemoDotNET_log.ldf.
I got the error when I try to see properties of database, or script
database or open in C#.NET or VB program.|||R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:
> I am using SQL Server 2005.
> In directory of the database I have files: DemoDotNET.mdf,
> DemoDotNET_log.ldf.
> I got the error when I try to see properties of database, or script
> database or open in C#.NET or VB program.

If you do

SELECT name, physical_name
FROM sys.master_files
where database_id = db_id('DemoDotNet')

does physical_name agree with the files you see in Explorer?

If they do, my suspicion is that the log file has been tampered with,
and is not usable.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

2005 Enterprise Edition to Standard Edition with Table Partitions

All,
I have an issue with Table Partitioning in that only Enterprise/Developer
Editions support this feature.
To explain this easier;
We have 2 servers, Live and Dev.
Live is using EE and Dev is using SE.
When the next Dev cycle starts a backup of Live is applied to Dev and the
developers create sql scripts for the new SPs etc.
However I'm looking at implementing Table Partitioning which restores on
Standard Edition but wont start the DB.
Does anyone have any suggestions/recommendations on what i can do to get the
backup restored and accessable to the Developers without installing EE on the
Dev Server or removing the partitions on the live server?> Does anyone have any suggestions/recommendations on what i can do to get
> the
> backup restored and accessable to the Developers without installing EE on
> the
> Dev Server or removing the partitions on the live server?
I suggest you use SQL Server Developer edition on the development server.
It's considerably less expensive than Standard and has the same features as
Enterprise but cannot be used in production.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:58D06E4B-AD2E-439A-902E-4026C43E20E3@.microsoft.com...
> All,
> I have an issue with Table Partitioning in that only Enterprise/Developer
> Editions support this feature.
> To explain this easier;
> We have 2 servers, Live and Dev.
> Live is using EE and Dev is using SE.
> When the next Dev cycle starts a backup of Live is applied to Dev and the
> developers create sql scripts for the new SPs etc.
> However I'm looking at implementing Table Partitioning which restores on
> Standard Edition but wont start the DB.
> Does anyone have any suggestions/recommendations on what i can do to get
> the
> backup restored and accessable to the Developers without installing EE on
> the
> Dev Server or removing the partitions on the live server?

2005 Enterprise Edition to Standard Edition with Table Partitions

All,
I have an issue with Table Partitioning in that only Enterprise/Developer
Editions support this feature.
To explain this easier;
We have 2 servers, Live and Dev.
Live is using EE and Dev is using SE.
When the next Dev cycle starts a backup of Live is applied to Dev and the
developers create sql scripts for the new SPs etc.
However I'm looking at implementing Table Partitioning which restores on
Standard Edition but wont start the DB.
Does anyone have any suggestions/recommendations on what i can do to get the
backup restored and accessable to the Developers without installing EE on th
e
Dev Server or removing the partitions on the live server?> Does anyone have any suggestions/recommendations on what i can do to get
> the
> backup restored and accessable to the Developers without installing EE on
> the
> Dev Server or removing the partitions on the live server?
I suggest you use SQL Server Developer edition on the development server.
It's considerably less expensive than Standard and has the same features as
Enterprise but cannot be used in production.
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:58D06E4B-AD2E-439A-902E-4026C43E20E3@.microsoft.com...
> All,
> I have an issue with Table Partitioning in that only Enterprise/Developer
> Editions support this feature.
> To explain this easier;
> We have 2 servers, Live and Dev.
> Live is using EE and Dev is using SE.
> When the next Dev cycle starts a backup of Live is applied to Dev and the
> developers create sql scripts for the new SPs etc.
> However I'm looking at implementing Table Partitioning which restores on
> Standard Edition but wont start the DB.
> Does anyone have any suggestions/recommendations on what i can do to get
> the
> backup restored and accessable to the Developers without installing EE on
> the
> Dev Server or removing the partitions on the live server?