SQL Anywhere database - SQL query puzzle

Author

SQL query puzzle

Morley Chalmers

2005-08-16, 8:23 pm

I've been working on this problem for several days. It's
time to turn to someone with greater depth in SQL than I.

I'm using Adaptive Server Anywhere with Servoy as the front
end. Servoy (Servoy.com) is a Java-based development
environment for SQL (all flavours) which comes bundled with
ASA by default.

Two tables, "Company" and "CR". Each Company record may or
may not have one or more related records in CR. Or none.

I need a SQL query which finds all companies whose most
recent related CR record is before or after a particular
date. The operative phrase here is "most recent CR".
Obviously requires a subquery.

Using the CR table I've discovered the following produces
the records I need. I'm suspicious of this code because it
selects the table's PK (crid). Since this is to be a
subquery of a query to the Company table I would have
thought selecting the comid foreign key field would have
been more practical. However all my attempts to build a
subquery based on selecting comid have failed.

So, here's the subquery code that works. One additional
note. Servoy provides its developers with a JavaScript
environment. I'm building up variables which the line "var
dataset, etc" passes to ASA for a response. The third
argument "[d1]" references the "?" at the very end of the
query variable.

+++++++++++++++++
var days = 7;
var maxReturnedRows = 100000;
var d1 = new Date();
d1.setDate(d1.getDate() - days); // reduce today to the
threshold date

var query = "SELECT cr1.crid FROM cr cr1, cr cr2 ";
query += "WHERE cr1.comid = cr2.comid ";
query += "GROUP BY cr1.crid HAVING MAX(cr1.creation_date) <?
";
var dataset =
databaseManager. getDataSetByQuery(co
ntroller.getServerName()
,query,& #91;d1],maxReturnedR
ows);
controller. loadRecords(dataset)
;
+++++++++++++++++

Selects and loads only CRs where the most recent CR is less
than a week old.

The above subquery now needs to be wrapped in a Company
query. The best I've come up with so far produces an
unexpected result.

+++++++++++++++++
var days = 7;
var maxReturnedRows = 100000;
var d1 = new Date();
d1.setDate(d1.getDate() - days); // reduce today to the
threshold date

var query = "SELECT company.company_id FROM company, cr cr1
WHERE ";
query += "(company.cr_status = 0 OR company.cr_status IS
NULL) ";
query += "AND company.company_id IN ";
query += "(SELECT cr1.crid FROM cr cr1, cr cr2 ";
query += "WHERE cr1.comid = cr2.comid ";
query += "GROUP BY cr1.crid HAVING MAX(cr1.creation_date) <?
) ";

var dataset =
databaseManager. getDataSetByQuery(co
ntroller.getServerName()
,query,& #91;],maxReturnedRow
s);
controller. loadRecords(dataset)
;
+++++++++++++++++

Selects just a single record and that one DOES NOT have any
related record in CR.

Been going round in circles on this one for a week. Learning
a lot about SQL queries but obviously this one is too
complex for this guy at this stage. Help appreciated.

Paul Horan[TeamSybase]

2005-08-17, 9:27 am

Try something like this:

Select * from COMPANY
where
exists
(select 1 from CR
where CR.comid = COMPANY.comid and
CR.creation_date <= :theDesiredDate )
and
not exists
(select 1 from CR
where CR.comid = COMPANY.comid and
CR.creation_date > :theDesiredDate)

That says: find any Company that has at least one CR prior to theDesiredDate, and NO CRs after the desired date.

--
Paul Horan[TeamSybase]

<Morley Chalmers> wrote in message news:43024703.1a21.1681692777@sybase.com...
> I've been working on this problem for several days. It's
> time to turn to someone with greater depth in SQL than I.
>
> I'm using Adaptive Server Anywhere with Servoy as the front
> end. Servoy (Servoy.com) is a Java-based development
> environment for SQL (all flavours) which comes bundled with
> ASA by default.
>
> Two tables, "Company" and "CR". Each Company record may or
> may not have one or more related records in CR. Or none.
>
> I need a SQL query which finds all companies whose most
> recent related CR record is before or after a particular
> date. The operative phrase here is "most recent CR".
> Obviously requires a subquery.
>
> Using the CR table I've discovered the following produces
> the records I need. I'm suspicious of this code because it
> selects the table's PK (crid). Since this is to be a
> subquery of a query to the Company table I would have
> thought selecting the comid foreign key field would have
> been more practical. However all my attempts to build a
> subquery based on selecting comid have failed.
>
> So, here's the subquery code that works. One additional
> note. Servoy provides its developers with a JavaScript
> environment. I'm building up variables which the line "var
> dataset, etc" passes to ASA for a response. The third
> argument "[d1]" references the "?" at the very end of the
> query variable.
>
> +++++++++++++++++
> var days = 7;
> var maxReturnedRows = 100000;
> var d1 = new Date();
> d1.setDate(d1.getDate() - days); // reduce today to the
> threshold date
>
> var query = "SELECT cr1.crid FROM cr cr1, cr cr2 ";
> query += "WHERE cr1.comid = cr2.comid ";
> query += "GROUP BY cr1.crid HAVING MAX(cr1.creation_date) <?
> ";
> var dataset =
> databaseManager. getDataSetByQuery(co
ntroller.getServerName()
> ,query,& #91;d1],maxReturnedR
ows);
> controller. loadRecords(dataset)
;
> +++++++++++++++++
>
> Selects and loads only CRs where the most recent CR is less
> than a week old.
>
> The above subquery now needs to be wrapped in a Company
> query. The best I've come up with so far produces an
> unexpected result.
>
> +++++++++++++++++
> var days = 7;
> var maxReturnedRows = 100000;
> var d1 = new Date();
> d1.setDate(d1.getDate() - days); // reduce today to the
> threshold date
>
> var query = "SELECT company.company_id FROM company, cr cr1
> WHERE ";
> query += "(company.cr_status = 0 OR company.cr_status IS
> NULL) ";
> query += "AND company.company_id IN ";
> query += "(SELECT cr1.crid FROM cr cr1, cr cr2 ";
> query += "WHERE cr1.comid = cr2.comid ";
> query += "GROUP BY cr1.crid HAVING MAX(cr1.creation_date) <?
> ) ";
>
> var dataset =
> databaseManager. getDataSetByQuery(co
ntroller.getServerName()
> ,query,& #91;],maxReturnedRow
s);
> controller. loadRecords(dataset)
;
> +++++++++++++++++
>
> Selects just a single record and that one DOES NOT have any
> related record in CR.
>
> Been going round in circles on this one for a week. Learning
> a lot about SQL queries but obviously this one is too
> complex for this guy at this stage. Help appreciated.

Richard Biffl

2005-08-17, 1:23 pm

Paul gave you one solution. Here is an alternative that uses a subquery and
is more similar to your code. Please test, because I didn't:

SELECT c.*, c2.lastcreationdate
FROM company c
JOIN (
SELECT comid, MAX(creation_date) lastcreationdate
FROM cr
GROUP BY comid
HAVING lastcreationdate < ?
) c2 ON c.companyid = c2.comid
WHERE c.cr_status = 0 OR c.cr_status IS NULL

Richard

Morley Chalmers

2005-08-18, 11:23 am

I've now received four distinctly different responses to
this problem from this and another forum. Two of them
tested, two of them work. Much thanks. Gives me something to
chew on. Here's the one I've chosen to go with:

var query = "SELECT * FROM company ";
query += "WHERE company.company_id IN ";
query += "(SELECT cr.comid FROM cr GROUP BY cr.comid HAVING
max(cr.creation_date) < ? ) ";
query += "AND (company.cr_status = 0 OR company.cr_status IS
NULL) ";

Kind regards

Sponsored Links