Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

count(*) using WHERE argument from separate tables?

Status
Not open for further replies.

kkffjjman

Technical User
Feb 7, 2008
11
CA
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?

I tried this but get an error...

Code:
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 & "%'"
 
That query seems to be along the right lines - join the two tables and count the result.

I expect it's either caused by this bit:
kc_messages.createdate >= to_date('" & hours_ago & "','mm/dd/yyyy hh:mi:ss " & am_or_pm(1) & "')
If hours_ago isn't a date in your stated format, or if am_or_pm(1) isn't a valid part of a date format mask.

Alternatively, it could be this bit:
and kc_folder.lable like '" & searchstr & "%'
If the column is actually named kc_folder.label.

It'd help if you said what error you're actually getting. It can also be useful to print out the value of sql - so you can see what's actually being passed to the database instead of inferring the values of variables.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top