Joe Kelly
On 9/19/06, Christopher Jordan <
[EMAIL PROTECTED]> wrote:
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/
_______________________________________________ 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/
