Allows you to select data from one or more tables.
Syntax:
SELECT fieldlist FROM tablename [selectoptions]
Or
SELECT fieldlist FROM tablename1 [alias1], tablenameN [aliasN] [selectoptions]
Selectoptions: [WHERE…] [GROUP BY…] [HAVING …] [ORDER BY…]
fieldlist can be * for selecting all fields or field1[,fieldN]
field: fieldname [AS fieldalias]
The SELECT command extracts data from SQL tables, making it available to the SQL() function or other SQL commands as sub select statements. The SQL() function makes the result available as data of type string (see SQL() for formatting) and this is the only mechanism in the GSAK macro language for moving information from the SQL database into GSAK macro or database variables.
The version of the SELECT command that uses several table names results in joining the tables. In such cases fieldnames in the WHERE clause must be fully qualified as to table and field separated by a period: tablename.fieldname. Both tablenames and fieldnames can be aliased.
Notes
When you join two or more tables you must use fully qualified field names: tablename.fieldname in the WHERE clause. Both tablenames and fieldnames can be aliased.
$status = dbtosql("caches","Code,Name,CacheType,Difficulty,Terrain","$_Install\macros\caches.txt","") $status = dbtosql("logs","lparent,ltype,lby,ldate","$_Install\macros\logs.txt","") $status = sql("select caches.code as code,caches.name as name,logs.ldate as LogDate,logs.lby as LoggedBy from caches,logs where caches.code = logs.lparent","") $html = sqltohtml($status,"Cache name with logs","Y") |
The example above shows you that in the WHERE clause you refer to source tables (e.g. products.productid) where as in the GROUP BY, HAVING and ORDER BY clause, you refer to the result field.