I liked it. It was simple, clean, and easy to read and
understand. I'm still a SQL hack and learn new and better things almost
every day (or at least every week) :).
Cheers,
Chris
David L. Penton wrote:
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/
|
_______________________________________________
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/
