"Jim Hamer" <[EMAIL PROTECTED]> wrote:
> 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.
Those are known as SUBSELECTS. They haven't been incorporated into
MySQL...yet.
> 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.
I didn't sleep last night and it's catching up with me so I can't think SQL
right now, but if it can't be done with a LEFT JOIN and a GROUP BY then you
may have to consider CREATE TEMPORARY TABLE as an intermediate step.
--
Steve Werby
President, Befriend Internet Services LLC
http://www.befriend.com/
---------------------------------------------------------------------
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
