SQL Syntax |
No aliasing allowed: can't say FROM contact c, person p
No join specifications allowed in the FROM clause.
Specified in WHERE clause, not in FROM clause.
Can't say: FROM person INNER JOIN contact ON contact_id
Must say: FROM person, contact WHERE contact.contact_id = person.contact_id
Left outer join: FROM person, contact WHERE contact.contact_id *= person.contact_id
Works as expected:
ORDER BY lastname ASC, firstname DESC
Works – but can't combine with ORDER BY
Can't say COUNT(*) – must specify field name to count.
Supported Functions with GROUP BY: • COUNT( ) • SUM( ) • MIN( ) • MAX( ) • AVG( ) - average (Sum divided by count)
Example:
SELECT category_idx, COUNT(name) FROM contact WHERE associate_id = 123 GROUP BY category_idx
Not understood. Won’t work.
Column renaming
SELECT count(name) as sumName, project.name as projName
The new name is used in the result sets column name instead of the actual name of the field. This is useful for distinguishing conflicting field names.
This query will work fine:
SELECT * FROM contact WHERE name='A' OR name='B'
This will not work:
SELECT * FROM contact WHERE name='A' OR name='B' OR name='C'
The underlying database translation technology does not like SQL with more than one OR statement.
You’ll get an error message back if you try to use more than one OR statement in your command.
Up: OLE-DB Provider Prev: Query Examples Next: Why have a special OLE DB provider Edit