Group by Clause for 3 tables

jfergy · April 13th, 2006, 06:43 PM

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!