RE: SQL Help (updated)
Had some incorrect info in there Carry on :-) Given the following journals with related documents. Journal A document1a - Jan 1, 2004 document2a - Jan 2, 2003 document3a - Dec 3, 2002 Journal B document1b - Jan 3, 2001 document2b - Jan 4, 2000 document3b - Dec 2, 1999 Journal C document1c - Dec 3, 2002 document2c - Jan 9, 2003 document3c - Jan 9, 2004 I would like to output to be like this Journal C - Jan 9, 2004 Journal A - Jan 1, 2004 Journal B - Jan 3, 2001 Anyone want to take a stab at it? Here is the table setup.. Journal: journalId, title, createDate Document: documentId, journalId, createDate Thanks! Mike [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Help (updated)
> Had some incorrect info in there Carry on :-) > > Given the following journals with related documents. > > Journal A > > document1a - Jan 1, 2004 > document2a - Jan 2, 2003 > document3a - Dec 3, 2002 > > Journal B > > document1b - Jan 3, 2001 > document2b - Jan 4, 2000 > document3b - Dec 2, 1999 > > Journal C > > document1c - Dec 3, 2002 > document2c - Jan 9, 2003 > document3c - Jan 9, 2004 > > I would like to output to be like this > > Journal C - Jan 9, 2004 > Journal A - Jan 1, 2004 > Journal B - Jan 3, 2001 > > Anyone want to take a stab at it? Try this: SELECT j.title, MAX(d.createdate) AS maxdocdate FROM journal j JOIN document d ON j.journalid = d.journalid GROUP BY j.title ORDER BY maxdocdate DESC You can format your date output however you'd like. HTH, Stephen [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL help (updated)
Okay, I still need help, but I've resolve part of my problem. I'm able now to have the list of ID numbers in one field by themselves. So, my new field value (MyColumn) looks like this: '5,2,3,4,45,7' I still need a way using Query of Queries to extract only the records that have the 'idnumber' in that list. I've tried: SELECT * FROM AllResults WHERE #idnumber# in MyColumn I get an error: Query Of Queries syntax error. Encountered "3 in custom2" at line 0, column 0. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition TIA, Dave -Original Message- From: Phillips, Dave Sent: Tuesday, August 16, 2005 2:16 PM To: CF-Talk Subject: SQL help Hi, I'm on Oracle 9i. I have a query (call it AllResults) that is returning a column (call it MyColumn) that's value looks like this: 'identifier|5,2,3,4,45,7' Each row may have a different 'identifier' and a different quantity of numbers. Basically, there are two values in this column. identifier & numberlist separated by a pipe Now, I need to build a query of queries that queries this existing recordset and only returns the records which has a number that I'm comparing against. I know I can do this in CF by looping over the recordset, but I'm trying to avoid that (for processing time sake). Here's the pseudo code of what I'm trying to do: (idnumber is a variable in my code and will be ONE of the numbers listed in the numberlist) select * from AllResults WHERE idnumber is found in the list of numbers located in the MyColumn field I only want the records that have 'idnumber' IN the list of numbers I'm sure this can be done with some SQL code and since I'm not a SQL guru, let alone Oracle, any help or suggestions would be appreciated. Thanks! Dave ** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, ALLTEL requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:215299 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL help (updated)
SELECT * FROM AllResults WHERE #idnumber# in MyColumn That probably needs to be WHERE #idnumber# IN (MyColumn) The values of an IN clause are supposed to be in parenthesis I believe. You may also need to do something about the quotes if they are part of the value list. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:215305 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL help (updated)
[EMAIL PROTECTED] wrote: > Okay, I still need help, but I've resolve part of my problem. I'm able now > to have the list of ID numbers in one field by themselves. So, my new field > value (MyColumn) looks like this: > > '5,2,3,4,45,7' > > I still need a way using Query of Queries to extract only the records that > have the 'idnumber' in that list. I've tried: > > SELECT * > FROM AllResults > WHERE #idnumber# in MyColumn SELECT * FROM AllResults WHERE ',' & MyColumn & ',' LIKE '%#idnumber#%' Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:215335 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL help (updated)
You realize that this is essentially a db design issue - right? The table shouldn't be holding lists of numbers - there should be a join table that does that job. Right? On 8/16/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Okay, I still need help, but I've resolve part of my problem. I'm able now > to have the list of ID numbers in one field by themselves. So, my new field > value (MyColumn) looks like this: > > '5,2,3,4,45,7' > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:215336 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL help (updated)
>That probably needs to be > WHERE #idnumber# IN (MyColumn) Ian, Yes, I tried that. I had typoed my e-mail, but in my code I had the parenthesis. That's what give the invalid comparison error. It doesn't seem to recognize the field. Dave ** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, ALLTEL requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:215394 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL help (updated)
This isn't a table. It's a search results query, and there is no other way to provide the information since cfindex only allows two custom fields. I have to pass it back to my search results page as a list. Dave -Original Message- From: Deanna Schneider [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 16, 2005 4:37 PM To: CF-Talk Subject: Re: SQL help (updated) You realize that this is essentially a db design issue - right? The table shouldn't be holding lists of numbers - there should be a join table that does that job. Right? On 8/16/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Okay, I still need help, but I've resolve part of my problem. I'm able now > to have the list of ID numbers in one field by themselves. So, my new field > value (MyColumn) looks like this: > > '5,2,3,4,45,7' > ** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, ALLTEL requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:215395 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
