List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:April 4 2001 8:24pm
Subject:Re: Help with SQL
View as plain text  
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.
Thread
Help with SQLUnknown Sender4 Apr
  • Re: Help with SQLSteve Werby4 Apr
  • One-way data transferEric Anderson4 Apr
  • Re: Help with SQLGerald Clark4 Apr