Select records by max(date) in another related table?

[RyanAG]

Here is the scenario:
Two tables-1)Prospects table, 2)Events table
Prospects table holds data on the individual and is assigned a unique number 'pkey'.
Events on each individual prospect is recorded in the Events table and is related back to the Prospect table by the foreign key also named 'pkey'. When an event is recorded for a prospect a creation date and a future date is recorded. The future date is the exact same as the creation date if the event is not recorded as a future event. It is different then the creation date if a future date is inputed.
Trying to write a utility in asp for sql server 2000 that will return Prospects that have events with no future dates. I need to query only Distinct Prospects with max future dates that are not beyond today.
Here is what I have so far. It looks good, but under thorough examination there are duplicates.

SELECT DISTINCT a.pkey, a.triggerdate
FROM events a WHERE a.triggerdate =
(SELECT MAX(triggerdate)
FROM events b
WHERE b.pkey = a.pkey
AND b.triggerdate < #02/10/02#);

Thank you,
Ryan

 

[tlbroadbent]

1) You haven't used the Prospects table in the query. Where does it come into play? Your query doesn't utilize creation date or future date. What is the triggerdate in relation to these columns?

2) There is no way that the results can have duplicate rows. The distinct clause will eliminate duplicates. How are you identifying duplicates?

3) The query you posted can be simplified as

SELECT a.pkey, triggerdate=max(a.triggerdate)
FROM events a
WHERE triggerdate<#02/10/02#
GROUP BY a.pkey

4) You can add the Prospects table to the query in the following manner.

SELECT
--Use actual column names from the prospects table
a.pkey, a.col2, a.col3, ...
triggerdate=max(a.triggerdate)
FROM prospects a
JOIN events b
ON a.pkey=b.pkey
WHERE triggerdate<#02/10/02#
GROUP BY a.pkey, a.col2, a.col3, ... Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[jcf27]

I have the following two tables --

Table A

Apkid, name, transaction (distinct records)

Table B

Bpkid, Bdate, Bvalue

Table B gets a new record every day. For example

1,10/01/02, 3
1,10/02/02, 5
1,10/03/03, 7

Apkid and Bpkid are the same.

The objective is to create a select statement where I have all the fields from Table A (no problem here) and the value of the latest date found in Table B (7 in this case). I tried using the MAX value in the join at no avail. It returns all rows from Table B.

Many thanks in advance!

J.