p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Classic ASP Professional (http://p2p.wrox.com/forumdisplay.php?f=63)
-   -   Group by Clause for 3 tables (http://p2p.wrox.com/showthread.php?t=41265)

jfergy April 13th, 2006 06:43 PM

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>




vinod_yadav1919 April 15th, 2006 04:30 AM

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

jfergy April 15th, 2006 06:21 PM

Thanks that worked great!



All times are GMT -4. The time now is 10:15 AM.

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