Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.

# Thread: Combining statements

1. ## Combining statements

Hi guys, can you explain to me how I can combine these statements into one please?

They all query the same table with different criteria so I guess it might be possible?

Thank you

Code:
```'find how many passes there are in this section
set passRS=conn.execute("SELECT count(criteria_level) AS passNo FROM z_"&strSchool&"_marks INNER JOIN z_"&strSchool&"_criteria ON marks_criteria_id = criteria_id WHERE criteria_level = 1 AND marks_username = '"&userRS("user_username")&"' AND critieria_section_no = "&strSectionRS)
strPass = passRS("passNo")
passRS.CLOSE()
SET passRS = NOTHING
'How many did student get?
set passTotalRS=conn.execute("SELECT count(criteria_level) AS passNo FROM z_"&strSchool&"_criteria WHERE criteria_level = 1 AND critieria_section_no = "&strSectionRS)
strTotalPass = passTotalRS("passNo")
passTotalRS.CLOSE
SET passTotalRS = NOTHING
'find how many merits there are in this section
set meritRS=conn.execute("SELECT count(criteria_level) AS meritNo FROM z_"&strSchool&"_marks INNER JOIN z_"&strSchool&"_criteria ON marks_criteria_id = criteria_id WHERE criteria_level = 2 AND marks_username = '"&userRS("user_username")&"' AND critieria_section_no = "&strSectionRS)
strMerit = meritRS("meritNo")
meritRS.CLOSE
SET meritRS = NOTHING
'How many did student get?
set meritTotalRS=conn.execute("SELECT count(criteria_level) AS meritNo FROM z_"&strSchool&"_criteria WHERE criteria_level = 2 AND critieria_section_no = "&strSectionRS)
strTotalMerit = meritTotalRS("meritNo")
meritTotalRS.CLOSE
SET meritTotalRS = NOTHING
'find how many distinctions there are in this section
set distinctRS=conn.execute("SELECT count(criteria_level) AS distinctNo FROM z_"&strSchool&"_marks INNER JOIN z_"&strSchool&"_criteria ON marks_criteria_id = criteria_id WHERE criteria_level = 3 AND marks_username = '"&userRS("user_username")&"' AND critieria_section_no = "&strSectionRS)
strDistinct = distinctRS("distinctNo")
distinctRS.CLOSE
SET distinctRS = NOTHING
'How many did student get?
set distinctTotalRS=conn.execute("SELECT count(criteria_level) AS distinctNo FROM z_"&strSchool&"_criteria WHERE criteria_level = 3 AND critieria_section_no = "&strSectionRS)
strTotalDistinct = distinctTotalRS("distinctNo")
distinctTotalRS.CLOSE
SET distinctTotalRS = NOTHING```

2. Probably still want to do two queries, since there are two really very separate cases.

But your DB appears badly designed. Why would you *EVER* create separate tables for different schools? Why not one table for all schools with just a field to say *which* school? If there were some kind of security question involved, it might make sense, except *THEN* you should be using separate databases with separate logins/passwords for each database. It makes no sense at all to have separate tables in a single database from any perspective I can think of.

Anyway:

There are two basic ways: Get all 3 criteria in one record or get 3 records, one with each criteria level.

So... first, all 3 in one record:

Code:
```marksSQL = "SELECT SUM(IF(criteria_level=1,1,0)) AS passNo, " _
& "           SUM(IF(criteria_level,2,1,0) AS meritNo, " _
& "          SUM(IF(criteria_level,2,1,0) AS distinctNo " _
& " FROM z_"&strSchool&"_marks, z_"&strSchool&"_criteria " _
& " WHERE marks_criteria_id = criteria_id " _
& " AND marks_username = '"&userRS("user_username")&"' " _
& " AND critieria_section_no = "&strSectionRS
Set RS = conn.Execute( marksSql )
passNo = RS("passNo")
meritNo = RS("meritNo")
distnctNo = RS("distincNo")
RS.Close

marksSQL = "SELECT SUM(IF(criteria_level=1,1,0)) AS passNo, " _
& "           SUM(IF(criteria_level,2,1,0) AS meritNo, " _
& "          SUM(IF(criteria_level,2,1,0) AS distinctNo " _
& " FROM z_"&strSchool&"_criteria " _
& " WHERE critieria_section_no = "&strSectionRS
Set RS = conn.Execute( marksSql )
totalPassNo = RS("passNo")
totalMeritNo = RS("meritNo")
totalDistnctNo = RS("distincNo")
RS.Close```
(I just could *NOT* bring myself to code that as `strPassNo = RS("passNo")` when *CLEARLY* the value of strPassNo would *NOT* be as string! It *WILL* be a number. A count (or sum) *MUST* be a number? Why would you ever named the variable str when it holds a number?

(Of course, I happen to be one of those who think that "Hungarian Notation" is an abomination. I have never opted to use prefixes on my variables such as "str" and "int" and "obj". Why MicroSLOP ever started that I will never know, but it's nice to know that even the Slop Shop has finally abandoned them, for the most part.)

3. ## Users who have thanked Old Pedant for this post:

generaltomfool (12-17-2012)

4. Then the other way: 3 records per query.
Code:
```marksSQL = "SELECT criteria_level, COUNT(*) AS criNo " _
& " FROM z_"&strSchool&"_marks, z_"&strSchool&"_criteria " _
& " WHERE marks_criteria_id = criteria_id " _
& " AND marks_username = '"&userRS("user_username")&"' " _
& " AND critieria_section_no = "&strSectionRS _
& " GROUP BY criteria_level ORDER BY criteria_level
Set RS = conn.Execute( marksSql )
passNo = RS("criNo")
RS.MoveNext
meritNo = RS("criNo")
RS.MoveNext
distnctNo = RS("criNo")
RS.Close

marksSQL = "SELECT criteria_level, COUNT(*) AS criNo " _
& " FROM z_"&strSchool&"_criteria " _
& " WHERE critieria_section_no = "&strSectionRS _
& " GROUP BY criteria_level ORDER BY criteria_level
Set RS = conn.Execute( marksSql )
totalPassNo = RS("criNo")
RS.MoveNext
totalMeritNo = RS("critNo")
RS.MoveNext
totalDistnctNo = RS("criNo")
RS.Close```
Ugh...just noticed.

You even have
Code:
`         & " WHERE critieria_section_no = "&strSectionRS _`
and again, strSectionRS is clearly a number. So why the "str" prefix?

If you are going to insist on using ugly Hungarian Notation, you should at least use it correctly: If a variable holds a number, it should never have a str prefix.

5. By the way, since this *IS* the MYSQL forum, my answers above are for MySQL queries.

If you are using Access, then you simply change IF to IIF in my first post.

If you are using SQL Server, you would have to use CASE WHEN in place of IF

The answers in the second post should work for most any DB.

6. Thank you - I think I understand what you have done and will experiment with your methods.

And WOW - no holds barred on the corrections - I will take them on board too, why 'Hungarian'? I am self taught (ouch!) and happily take these tips on board.

Thank you for taking the time to help me!

7. LOL! Teach me to assume!

I just assumed you had been reading MS recommended practices from 10 or more years ago. MS was very strongly into the so-called Hungarian Notation until .NET came along, at which time they eased up quite a bit.

As for why "Hungarian": http://en.wikipedia.org/wiki/Hungarian_notation

8. ## Users who have thanked Old Pedant for this post:

generaltomfool (12-20-2012)

9. I've implemented your code, it has HALVED page load time - thank you very much!
Can you recommend a site I can look at this level of tutorials (or dare I say a book I could read...?) I can use to get my knowledge up please?

Thanks again,

Jon

10. I hate to disappoint you, but I've never found a book that teaches SQL tricks.

I've either learned them all on my own or seen somebody use them first. Or I've seen something and then tried to improve it.

My primary resource for learning MySQL (I had previously used SQL Server and Access SQL) was just the MySQL documentation:
http://dev.mysql.com/doc/refman/5.5/en/index.html

I actually came up with the SUM(IF(...),1,0) trick on my own maybe 12 years ago, when I was using Access (Access uses IIF instead of IF, but otherwise the same). Only to then find out a year or so later that it's a standard technique in SQL Server (where you have to use CASE WHEN instead off IIF, but concepts are the same). Anyway, when I started using MySQL heavily (starting in 2009) it was an easy transition.

MySQL has some impressively powerful features. Partitioned tables are a wonderful example. I used them to increase the performance of removing old records by a factor of maybe 100 or more. But it also has some strange limitiations. Its use of indexes, for example, is really really poor compared to SQL Server. (SQL Server will do index-only JOINs to get the number of records fetched way way down before it actually has to go get the records, per se. MySQL won't do that, at all. It's a huge limitation on performance.)

Anyway, what it comes down to is: practice, practice, practice. I actually *PLAY* with MySQL almost every day. (Well, I play with HTML and JavaScript every day, too, come to think of it.) That is, I purposely try things that I don't actually need as part of my work for that day, just to see if they work better. Then when I have to actually implement something "on the job", I have more weapons in my coding arsenal to choose from.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•