Joins, Aggregates, and Transactions

Combine tables, compute statistics, and group operations for safety.

INNER JOIN
Set rs = conn.Execute(_
'SELECT e.id, c.title, u.username ' &
'FROM enrollments e ' &
'JOIN courses c ON e.course_id = c.id ' &
'JOIN users u ON e.user_id = u.id ')

Aggregate Functions
SELECT COUNT(*) FROM lessons WHERE course_id = 1
SELECT AVG(score) FROM results
SELECT MAX(sort_order) FROM lessons

GROUP BY
SELECT course_id, COUNT(*) AS cnt FROM lessons GROUP BY course_id

Transactions
conn.Execute 'BEGIN TRANSACTION'
conn.Execute 'DELETE FROM enrollments WHERE user_id=' & id
conn.Execute 'DELETE FROM users WHERE id=' & id
conn.Execute 'COMMIT'

On Error Rollback Pattern
On Error Resume Next
conn.Execute 'BEGIN TRANSACTION'
conn.Execute sql1
If Err.Number <> 0 Then conn.Execute 'ROLLBACK' : Err.Clear : Exit Sub
conn.Execute sql2
If Err.Number <> 0 Then conn.Execute 'ROLLBACK' : Err.Clear : Exit Sub
conn.Execute 'COMMIT'

LIKE and ORDER BY
SELECT * FROM courses WHERE title LIKE '%Request%' ORDER BY created_at DESC

Live Demo →