Re: SQL Subquery on Max(date)
- From: Bob Hairgrove <invalid@xxxxxxxxxxx>
- Date: Sun, 22 Apr 2007 16:00:08 +0200
On 20 Apr 2007 09:42:25 -0700, chollstein@xxxxxxxxxxxxxxxxxxxxxxxxx wrote:
Hi,
I'm trying to outer join to a maximum date value using a subquery in
order to return company information and the last activity date
associated. The basic working "sub" query is:
SELECT actcomp.company_id, MAX(act.due_date)
FROM oncd_activity_company AS actcomp, oncd_activity AS act
WHERE actcomp.activity_id = act.activity_id
GROUP BY company_id, due_date
The overall (abbreviated) query I'm trying to insert this select into
is:
SELECT oncd_company.company_id,
oncd_company.company_name_1,
act.due_date
FROM oncd_company
LEFT OUTER JOIN oncd_activity_company ON (oncd_company.company_id =
oncd_activity_company.company_id)
LEFT OUTER JOIN (SELECT actcomp.company_id, MAX(act.due_date)
FROM oncd_activity_company AS actcomp, oncd_activity AS act
WHERE actcomp.activity_id = act.activity_id
GROUP BY company_id, due_date) ON
(oncd_activity_company.company_id = actcomp.company_id)
I'm receiving an "invalid syntax near keyword ON" error (highlight
appears on the period in "oncd_activity_company.company_id").
Any help would be appreciated!
Thanks,
Chris.
I'm not sure, but it looks like your design might be giving you problems.
"due_date" should be in the table which joins activities to companies, as I see
it. Does your database design look something like this? For example:
CREATE TABLE oncd_company (
company_id LONG NOT NULL,
company_name_1 VARCHAR(255) NOT NULL,
CONSTRAINT pk_oncd_company
PRIMARY KEY (company_id),
CONSTRAINT uk_oncd_company
UNIQUE (company_name_1)
);
CREATE TABLE oncd_activity (
activity_id LONG NOT NULL,
activity_descr VARCHAR(255) NOT NULL,
CONSTRAINT pk_oncd_activity
PRIMARY KEY (activity_id),
CONSTRAINT uk_oncd_activity
UNIQUE (activity_descr)
);
CREATE TABLE oncd_activity_company (
company_id LONG NOT NULL,
activity_id LONG NOT NULL,
due_date DATETIME NOT NULL,
CONSTRAINT pk_oncd_activity_company
PRIMARY KEY (company_id, activity_id),
CONSTRAINT fk_company
FOREIGN KEY (company_id)
REFERENCES oncd_company (company_id),
CONSTRAINT fk_activity
FOREIGN KEY (activity_id)
REFERENCES oncd_activity (activity_id)
);
If so, then your queries would become much simpler. However, I had to use two
queries for this. I am still working with Access 97, but maybe this works better
in later versions? There is a trick using the "SELECT ... FROM ..[SELECT...] ..."
syntax to include a subquery in a SELECT statement. However, it didn't work for
this scenario.
Anyway, Query1 would look like this:
SELECT oncd_activity_company.company_id, oncd_activity_company.activity_id,
oncd_activity.activity_descr, Max(oncd_activity_company.due_date) AS
Last_Activity
FROM oncd_activity INNER JOIN oncd_activity_company ON oncd_activity.activity_id
= oncd_activity_company.activity_id
GROUP BY oncd_activity_company.company_id, oncd_activity_company.activity_id,
oncd_activity.activity_descr;
Since I have defined activity_id as NOT NULL and primary key for the table
ondc_activity, you don't need n outer join for this one which you can use to
base the second query upon. Query2 would look like this:
SELECT oncd_company.company_name_1,
q1.activity_descr,
q1.Last_Activity
FROM oncd_company LEFT JOIN q1 ON oncd_company.company_id = q1.company_id;
You can also include the "_id" fields in the output of the 2nd query if
necessary.
If you need to do this all in code, I would just create a dummy select query for
Query1 whose SQL statement can be replaced at runtime with the corresponding SQL
statement given above (or whatever you end up with).
Of course, the use of surrogate keys (i.e., the "_id" columns) as primary keys
when there are unique constraints on the textual columns is technically not
necessary. However, if surrogate keys are used, which is a subject to debate all
by itself, then the unique constraints become necessary for obvious reasons. I
have done it this way for simplicity, and because you seem to be using them in
your setup.
--
Bob Hairgrove
NoSpamPlease@xxxxxxxx
.
- Follow-Ups:
- Re: SQL Subquery on Max(date)
- From: Bob Hairgrove
- Re: SQL Subquery on Max(date)
- References:
- SQL Subquery on Max(date)
- From: chollstein
- SQL Subquery on Max(date)
- Prev by Date: Re: Create a query in code
- Next by Date: Re: Calculating the Sum IF fields equal criteria
- Previous by thread: Re: SQL Subquery on Max(date)
- Next by thread: Re: SQL Subquery on Max(date)
- Index(es):
Relevant Pages
|
