SQL Subquery on Max(date)

C

chollstein

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.
 
D

Douglas J. Steele

Try:

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) AS X ON
(oncd_activity_company.company_id = X.company_id)
 
C

chollstein

Try:

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) AS X ON
(oncd_activity_company.company_id = X.company_id)
Click to expand...
Received new error after inserting the "AS X" and "X.company_id"
strings per above:
"No column was specified for column 2 of 'X'.
The multi-part identifier "act.due_date" could not be bound."

Thanks for the help though!
 
C

chollstein

Try:

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) AS X ON
(oncd_activity_company.company_id = X.company_id)

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)












- Show quoted text -
Click to expand...

Not sure if the last post was appended - New error as a result of
adding the "AS X" and "X.company_id" text: "No column was specified
for column 2 of 'X'.
The multi-part identifier "act.due_date" could not be bound."
 
D

Douglas J. Steele

Received new error after inserting the "AS X" and "X.company_id"
strings per above:
"No column was specified for column 2 of 'X'.
The multi-part identifier "act.due_date" could not be bound."
Click to expand...

I just noticed that your SELECT query isn't valid.

Since you're trying to get the maximum value of act.due_date for each
company_id, due_date shouldn't be included in the GROUP BY phrase.
 
C

chollstein

I just noticed that your SELECT query isn't valid.

Since you're trying to get the maximum value of act.due_date for each
company_id, due_date shouldn't be included in the GROUP BY phrase.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)- Hide quoted text -

- Show quoted text -
Click to expand...

Moved due_date out of the GROUP BY:

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) AS X ON (oncd_activity_company.company_id =
X.company_id)

(also tried different string for "X") and still receiving the error:
"No column was specified for column 2 of 'X'.
The multi-part identifier "act.due_date" could not be bound.
 
D

Douglas J. Steele

I thnk a problem may be that your SELECT is trying to refer to act.due_dat.
I'm also wondering whether you need to specify an alias for the Max function
in the subselect.

SELECT oncd_company.company_id,
oncd_company.company_name_1,
X.MaxDate
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) AS MaxDate
FROM oncd_activity_company AS actcomp INNER JOIN oncd_activity AS act
ON actcomp.activity_id = act.activity_id
GROUP BY company_id) AS X ON
(oncd_activity_company.company_id = X.company_id)
 
B

Bob Hairgrove

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.
Click to expand...

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, asI 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 usetwo
queries for this. I am still working with Access 97, but maybe this worksbetter
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 useto
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 primarykeys
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.
 
B

Bob Hairgrove

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)
);
Click to expand...

Actually, all three columns should go into the composite primary key here:
...
CONSTRAINT pk_oncd_activity_company
PRIMARY KEY (company_id, activity_id, due_date),
...
etc. Otherwise, it doesn't make sense to do the GROUP BY on company_id and
activity_id.
 
Top