Anyone? HELP!!!

[Jeff Uchtman]

Here are 4 different MS SQL query's:


Set rsVer = Server.CreateObject("ADODB.Recordset")
strSql = "Select p.Locality_Code, p.Name, Count(c.Locality_Code) as
Verified_Count From County AS p LEFT JOIN Verified as c on
p.Locality_Code=c.Locality_Code Group BY p.Locality_Code, p.Name ORDER BY
p.Locality_Code"
rsVer.Open strSQL, Conn

Set rsPetA = Server.CreateObject("ADODB.Recordset")
strSql = "Select p.Locality_Code, p.Name, Count(c.Locality_Code) as
PetA_Count From County AS p LEFT JOIN Verified as c on
p.Locality_Code=c.Locality_Code and PetA = 1 Group BY p.Locality_Code,
p.Name ORDER BY p.Locality_Code"
rsPet1.Open strSQL, Conn

Set rsPetB = Server.CreateObject("ADODB.Recordset")
strSql = "Select p.Locality_Code, p.Name, Count(c.Locality_Code) as
PetB_Count From County AS p LEFT JOIN Verified as c on
p.Locality_Code=c.Locality_Code and PetB = 1 Group BY p.Locality_Code,
p.Name ORDER BY p.Locality_Code"
rsPet2.Open strSQL, Conn

Set rsPetC = Server.CreateObject("ADODB.Recordset")
strSql = "Select p.Locality_Code, p.Name, Count(c.Locality_Code) as
PetC_Count From County AS p LEFT JOIN Verified as c on
p.Locality_Code=c.Locality_Code and PetC = 1 Group BY p.Locality_Code,
p.Name ORDER BY p.Locality_Code"
rsPet3.Open strSQL, Conn

Set rsPetD = Server.CreateObject("ADODB.Recordset")
strSql = "Select p.Locality_Code, p.Name, Count(c.Locality_Code) as
PetD_Count From County AS p LEFT JOIN Verified as c on
p.Locality_Code=c.Locality_Code and PetD = 1 Group BY p.Locality_Code,
p.Name ORDER BY p.Locality_Code"
rsPet4.Open strSQL, Conn

As you can see all are tapping the same tables for results but the 2nd, 3rd,
and 4th are filtering by a table if checked. How can I combine all 4 into 1
with the filters involved? Each query give me a result from 93 variables
showing Locality_Code (1 - 93), Name (Name of each Locality_Code) and then
the count, first total, then filtered by PetA, PetB, PetC, PetD. I'd like
to do a loop through to display the results but not sure if thats the best
way.

Thanks
Jeff

 

[Bullschmidt]

Well perhaps using a UNION query might work or here is something I put
together about using a subquery.

Example of one query (QueryB) based on the results of another query
(QueryA):

QueryA = "SELECT CustID FROM tblCUSTOMERS WHERE CustName = 'A%'"

QueryB = "SELECT CustID, CustName FROM tblCUSTOMERS WHERE CustID IN (" &
QueryA & ")"

But the following is even faster and allows for more than one field to
be returned in QueryA:

QueryB = "SELECT tblCUSTOMERS.CustID, CustName FROM (" & strSQLA & ") AS
tblSQLA INNER JOIN tblCUSTOMERS ON tblSQLA.CustID = tblCUSTOMERS.CustID"

So QueryA would include all the CustID's for customers starting with A.

And QueryB would include more fields in the customers table (i.e. not
just the CustID field) for the records returned in QueryA (which was the
customers starting with A).

I suppose it wouldn't hurt to always use LEFT JOIN's in QueryB and build
from the tblSQLA on the left to other tables that have fields you want
to return.

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...