Jim Hamer wrote:
> 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.
> Jim Hamer
Nested Selects are not supported yet.