That style of query has just been 'programmed' into my head for several years now [especially being date-related]. Seems so common
that it needs to be done, and there are several good solutions out there to do exactly the same thing [] using a correlated subquery
[] using an inner join on a derived[/immediate] view, and [] using EXISTS with a grouped predicate. I tend to use the third one,
mostly because I see a good bit of elegance in that style solution (it is more reminiscent of Set Algebra) and EXISTS is normally
optimized 'better' IMHO.
Each has its benefits for the particular solution.
hth,
David
Christopher Jordan wrote:
Well, my point was to find out if it was absolutely necessary to find
the *latest* status code or if you could just exclude any project IDs
that ever had a status of 5 associated with them. From your response it
seems that you do indeed need the latest status since a project could be
closed and then re-opened.
It looks like David's proposed solution already takes that into account
though.
Good luck! :)
Chris
Jake McKee wrote:
Hadn't thought that far but yes. If it was reopened, another status
entry would simply tnot be included this this query (since the latest
status_state would not be "5"). So really, it shouldn't matter...right?
Christopher Jordan wrote:
you said, "Where the Latest status update is..." does that mean that
a project can be closed and then reopened?
Chris
Jake McKee wrote:
OK, so where's what I have:
Clients Table (client_id, client_name)
Projects Table (project_id, project_client_idfk, project_name)
Status table (status_id, status_content, status_state,
status_project_idfk, status_add_date)
One client can have multiple projects. A single project can have
multiple status updates.
I'm trying to create a query that will output the following:
For each client, spit out all projects where the latest status
update is not of status type 5 (closed)
So:
Client 1
- project 1
- project 3
Client 2
- project 4
- project 6
...and so on. Projects 2 and 5 have a status entry that's set to a
value of 5, therefore are closed and don't need to be displayed.
How can I make this happen?
Thanks!
Jake
_______________________________________________
Reply to DFWCFUG:
[email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.HostMySite.com
www.teksystems.com/
