Hello Listers,
I am using mysql and I am trying to get a SQL statement to work. I have
designed a query in Microsoft Access and I am changing the syntax so that
it will run from Perl against a mysql database. Here is the code:
# from Access
#SELECT DISTINCTROW Query1.licenceNo, Query1.subCCYYMM
#FROM Query1
#WHERE (((Query1.licenceNo) In (SELECT [licenceNo] FROM [Query1] As Tmp
GROUP BY [licenceNo] HAVING Count(*)=1 )) AND ((Query1.subCCYYMM)="200103"))
#ORDER BY Query1.licenceNo;
# in the Perl program
$sql = "SELECT licenceNo, provinceCode ".
"FROM Query1 ".
"WHERE subCCYYMM = '200103' and licenceNo IN
(SELECT licenceNo FROM Query1 As Temp GROUP BY licenceNo HAVING COUNT(*)=1) ".
"ORDER BY licenceNo ";
The server chokes on the SELECT in the WHERE statement. I have a great
mysql book, but it doesn't cover a SELECT in a WHERE clause.
The background is as follows. Table Query1 is a historical file with lots
of records. Records have a unique index of licenceNo and subCCYYMM. I am
trying to find records that have only one occurrence for a licenceNo and
the subCCYYMM is 200103. That is, find only the historical records for a
licenceNo where March is the only record.
Any suggestions would be helpful.
Thanks,
Jim
Jim Hamer
Downtown Data Processing Inc.
(416) 489-3298 home and office
[EMAIL PROTECTED]
Toronto, Ontario, Canada
http://webhome.idirect.com/~jhamer/
