Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Professional For advanced coder questions in ASP 3. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 13th, 2006, 06:43 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default Group by Clause for 3 tables

Ok I have 3 tables:

Table 1: WaterBodies
WaterBodyID
WaterBodyName

Table 2: Listings
ListingID
Status (active = 1)

Table 3: WaterbodyJoin
WaterBodyID
ListingId

How could I get the WaterBodyname to appear and get the listcount of active listings in a select field. I have the following now:
Code:
<select name="WaterBody">
<option value="">Select WaterBody</option>
<%
set waterRst = GetFromDatabase ("Select * from tblWaterBodies Order by WaterBodyname")
Do While not(waterRst.EOF)%>
<option value="<%=waterRst("WaterBodyID")%>"><%=waterRst("WaterBodyName")%></option>
<% waterRst.MoveNext 
Loop
set waterRst = nothing %>
</select>


  #2 (permalink)  
Old April 15th, 2006, 04:30 AM
Friend of Wrox
 
Join Date: Oct 2004
Location: delhi, delhi, India.
Posts: 553
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to vinod_yadav1919 Send a message via Yahoo to vinod_yadav1919
Default

Hii jfergy!!
try this

 SELECT WaterBodies.WaterBodyName, COUNT(Listings.Status) AS totalActive
FROM WaterBodies
INNER JOIN WaterbodyJoin
          ON WaterBodies.WaterBodyID= WaterbodyJoin.WaterBodyID
 INNER JOIN Listings
          ON Listings.ListingId = WaterbodyJoin.ListingId
GROUP BY WaterBodies.WaterBodyName, Listings.Status
HAVING (Listings.Status = 1)

hope this will help you


Cheers :)

vinod
  #3 (permalink)  
Old April 15th, 2006, 06:21 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks that worked great!



Similar Threads
Thread Thread Starter Forum Replies Last Post
Group Within another Group, xslt1.0 jhansib4u BOOK: XSLT Programmer's Reference, 2nd Edition 4 November 22nd, 2007 01:24 AM
Restart new group number in Group Footer sukarso Crystal Reports 2 October 13th, 2006 12:11 PM
Selecting top record of a group by clause lic023 Access 7 June 7th, 2006 11:25 AM
Group by , Sub Group by and Sum mateenmohd SQL Server 2000 1 March 29th, 2005 09:51 AM
HAVING clause Adam H-W SQL Server ASP 2 February 11th, 2004 01:37 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.