Phucon-
SELECT a.companyid, CDate(Int(a.entrydate)) AS DateEntry, a.lastupdate, CDate(Int(e.entrydate)) AS DateEntryPrev, e.lastupdate AS LastUpdatePrev
FROM tblReview AS a INNER JOIN tblReview As e ON a.companyid = e.companyid
WHERE (((a.entrydate)=(select max(entrydate) from tblReview as b where b.companyid =a.companyid))) AND (((e.entrydate)=(select max(entrydate) from tblReview as c where c.companyid = d.companyid and c.entrydate < (select max(entrydate) from tblReview as d where d.companyid = a.companyid))))
ORDER BY a.companyid, CDate(Int(a.entrydate)) DESC;
Hello Mr. Viescas sorry about that.
Let me describe that again. I have 2 separated queries, one query pulls all the most recent dates records, and the other query pulls the records just right before the most recent date records.
e.g.
Query1
SELECT DISTINCT a.companyid, a.entrydate, a.lastupdate
FROM tblReview AS a
WHERE a.entrydate=(select max(b.entrydate) from tblReview as b Where b.companyid = a.companyid)
ORDER BY a.companyid, a.entrydate DESC;
Query2
SELECT DISTINCT a.companyid, a.entrydate, a.lastupdate
FROM tblReview AS a
WHERE (((a.entrydate)=(select max(b.entrydate) from tblReview as b where b.companyid =a.companyid and b.entrydate < (select max(c.entrydate) from tblReview as c where c.companyid = a.companyid))));
Since the first query contains more records than the second query. I use an Outer Join to return all records.
Join the 2 queries
Select Q1.*, Q2.* From Query1 As Q1 Left Join Query2 As Q2;
Now I am trying to learn how to reconstruct the "one-step" query (as shown below) to get the Outer Join result as the multiple steps query above.
SELECT DISTINCT a.companyid, CDate(Int(a.entrydate)) AS DateEntry, a.lastupdate, CDate(Int(e.entrydate)) AS DateEntryPrev, e.lastupdate AS LastUpdatePrev
FROM tblReview AS a INNER JOIN tblReview As e ON a.companyid = e.companyid
WHERE (((a.entrydate) Is Not Null And (a.entrydate)=(select max(entrydate) from tblReview as b where b.companyid =a.companyid))) AND (((e.entrydate)=(select max(entrydate) from tblReview as c where c.companyid = d.companyid and c.entrydate < (select max(entrydate) from tblReview as d where d.companyid = a.companyid))))
ORDER BY a.companyid, CDate(Int(a.entrydate)) DESC;
At first I thought I just need to change the Join, from Inner Join to Outer Join. I then realized the query is actually using the same tables, which is not going to work that way. It is more complicated. Isn't it? As a matter of fact, the "multiple steps" query runs faster then the "one-step". But it is always good to learn different techniques…
Thank you Mr. Viescas.
Phucon
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (7) |
Tidak ada komentar:
Posting Komentar