Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Community Forums
 Code Support: ASP (Non-Forum Related)
 Counting records in two tables		  New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

redbrad0
Advanced Member

USA
3725 Posts
Posted - 28 July 2003 :  22:24:54  Show Profile  Visit redbrad0's Homepage  Send redbrad0 an AOL message
For some reason I am having a hard time of getting the exact number of records. Here is the database layout..


DIRECTORY
   DIR_ID
   DIR_CATID
   DIR_NAME

DIRECTORY_CAT
   DIRCAT_ID
   DIRCAT_NAME


Here is what I want it to return. How many records are in the directory and how many categorys there are also. ALSO if there is now items linked to the category it wont count and if a category has 4 or 5 items in it, it only counts the category once. If this does not make since then just let me know.
Brad
Oklahoma City Online Entertainment Guide
Oklahoma Event Tickets

Nikkol
Forum Moderator

USA
6907 Posts
Posted - 28 July 2003 :  22:36:53  Show Profile
something like

select count(dir_id) from (select distinct dir_id from directory inner join directory_cat on directory.dir_catid = directory_cat.dircat_id)
Nikkol ~ Help Us Help You | ReadMe | 3.4.03 fixes | security fixes ~
Go to Top of Page

redbrad0
Advanced Member

USA
3725 Posts
Posted - 28 July 2003 :  23:39:49  Show Profile  Visit redbrad0's Homepage  Send redbrad0 an AOL message
well here is the exact code i used


	strSql = "select count(DIR_ID) as TotalCount"
	strSql = strSql & " from (select distinct DIR_ID"
	strSql = strSql & " from " & strTablePrefix & "DIRECTORY"
	strSql = strSql & " inner join " & strTablePrefix & "DIRECTORYCAT"
	strSql = strSql & " on " & strTablePrefix & "DIRECTORYCAT.DIRCAT_ID = " & strTablePrefix & "DIRECTORY.DIR_CATID)"
	set rs = my_Conn.Execute (strSql)

	if rs.eof or rs.bof then

	else
		Response.Write "TotalCount-" & rs("TotalCount") & "-<br>"
	end if


When I do this it returns 5 which is wrong, it should be 7.

There are 4 items in the FORUM_DIRECTORYCAT and 5 in FORUM_DIRECTORY. Two of the records in FORUM_DIRECTORYCAT do not have anything related to them so therefor should not be counted. It is ok if it uses two querys I am not worried about that.

Thanks for trying Nikkol
Brad
Oklahoma City Online Entertainment Guide
Oklahoma Event Tickets
Go to Top of Page

Nikkol
Forum Moderator

USA
6907 Posts
Posted - 29 July 2003 :  00:25:14  Show Profile
so where does 7 come from? how can the count of the items in the directory be greater than 5 if there are only 5 in there?
Nikkol ~ Help Us Help You | ReadMe | 3.4.03 fixes | security fixes ~
Go to Top of Page

redbrad0
Advanced Member

USA
3725 Posts
Posted - 29 July 2003 :  14:55:30  Show Profile  Visit redbrad0's Homepage  Send redbrad0 an AOL message
well i also needed to count the categorys that were assigned i finally just figured it out and was coming here to post that i fixed it.
Brad
Oklahoma City Online Entertainment Guide
Oklahoma Event Tickets
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.27 seconds. Powered By: Snitz Forums 2000 Version 3.4.07