count(*) using WHERE criteria from separate tables?

[kkffjjman]

Hello Everyone,

I need to build a query and I'm having a terrible time getting my head around it. Here are the details of the tables:

Table1: MESSAGES
Table2: FOLDERS

Each message, in the messages table is assigned a folder by FOLDER_ID.

I want to count messages in the MESSAGES table that match a FOLDER_NAME, and not the FOLDER_ID. The folder name is stored in the FOLDERS table.

The folder names change over time, so I use "LIKE" to find keywords in the folder name.

SO - how do I structure the query to say:


SELECT count(*) from MESSAGES where completed = 0 AND FOLDERID = ??????? (name?)

You see, I want to query by folder name which is in a separate table. Any ideas?

 

[gmmastros]

Try this...

Select Count(*) As MessageCount
From   FOLDERS
       Inner join Messages
         On Folders.Folder_Id = Messages.Folder_Id
Where  Folder_Name Like '%[!]SearchStringHere[!]%'

If this works, and you'd like me to explain it, just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kkffjjman]

didn't quite work..i get an sql err now

sql = "select count(*) from kc_message inner join kc_folders on kc_Messages.folderid = kc_folders.folderid where kc_messages.completedate is null and (kc_messages.createdate >= to_date('" & hours_ago & "','mm/dd/yyyy hh:mi:ss " & am_or_pm(1) & "')) and kc_folder.lable like '" & searchstr & "%'"

--the to_date portion isn't the problem as it works well in other statements i use.

Also, im now thinking my db is oracle, not ms.

 

[kkffjjman]

That didn't do it...now i get an SQL error:

ORA-00933: SQL command not properly ended

 

[gmmastros]

You are using ORACLE, but asking a question in a Microsoft SQL Server forum. I recommend you ask your question in one of the Oracle forums here at tek-tips.

Good luck.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom