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.
>
> Thanks,
> Jim
>
> Jim Hamer
>
Nested Selects are not supported yet.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
