Re: cfquery select question
Mark, there is an excellent SQL tutorial here: http://www.w3schools.com/sql/ If you figure out exactly how joins work, it will make writing SQL so much easier for you. Sub-selects like Greg used are also very useful. A few hours spent on this site will save you hours of struggle later. On Thu, May 26, 2011 at 11:18 AM, Mark Atkinson ma...@aocs.org wrote: Greg and John, Many thanks to you both. John's SQL produced exactly the same results as my tortuous attempt, and went a long way toward showing how to write better SQL. Greg, to broaden my SQL experience even further, I will have to take a little time to understand yours and get it to produce the same results, which I will do. Thanks again, Mark ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344952 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery select question
SELECT DISTINCT table3.ID, table3.Company FROM table3 INNER JOIN table2 ON table3.ID = table2.t2_ID INNER JOIN table1 ON table2.ordernumber = table1.ordernumber WHERE (table1.productcode LIKE '%a certain string%') On Thu, May 26, 2011 at 11:41 AM, Mark Atkinson ma...@aocs.org wrote: Hallo all, Forgive my SQL injection into this list - perhaps an answer to my question might also prove useful to others. I'm using three tables. There is no relationship between table1 and table3. I first query table1 for ordernumber and productcode, based on the productcode containing a certain string. Outputting the results, I query table2 for ordernumber and t2_ID based on table2.ordernumber matching table1.ordernumber. Looping those results, I query table3 for ID and Company based on table2.t2_ID. This, rather painfully I do admit, finds the table3.Company(ies) that has(have) purchased table1.productcode. Have Googled and read about how a single select with JOIN and/or UNION would achieve these results, but they also mention how I should essentially start at the end and work my way backwards, so to speak. Can't quite get my head around this. TIA for any advice. Mark ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344917 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery select question
If they all return 1 row use this.. select * from table3 t3 where t3.ID = ( select t2.ID from table2 t2 where t2.productcode = ( select t1.productcode from table1 t1 where t1.ordernumber = [whatever] ) ) If they return multiple rows you can change the = to IN () And if they return many rows, may want to use WHERE EXISTS.. Let us know On Thu, May 26, 2011 at 11:41 AM, Mark Atkinson ma...@aocs.org wrote: Hallo all, Forgive my SQL injection into this list - perhaps an answer to my question might also prove useful to others. I'm using three tables. There is no relationship between table1 and table3. I first query table1 for ordernumber and productcode, based on the productcode containing a certain string. Outputting the results, I query table2 for ordernumber and t2_ID based on table2.ordernumber matching table1.ordernumber. Looping those results, I query table3 for ID and Company based on table2.t2_ID. This, rather painfully I do admit, finds the table3.Company(ies) that has(have) purchased table1.productcode. Have Googled and read about how a single select with JOIN and/or UNION would achieve these results, but they also mention how I should essentially start at the end and work my way backwards, so to speak. Can't quite get my head around this. TIA for any advice. Mark ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344918 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfquery select question
Greg and John, Many thanks to you both. John's SQL produced exactly the same results as my tortuous attempt, and went a long way toward showing how to write better SQL. Greg, to broaden my SQL experience even further, I will have to take a little time to understand yours and get it to produce the same results, which I will do. Thanks again, Mark -Original Message- From: Greg Morphis [mailto:gmorp...@gmail.com] Sent: Thursday, May 26, 2011 11:48 AM To: cf-talk Subject: Re: cfquery select question If they all return 1 row use this.. select * from table3 t3 where t3.ID = ( select t2.ID from table2 t2 where t2.productcode = ( select t1.productcode from table1 t1 where t1.ordernumber = [whatever] ) ) If they return multiple rows you can change the = to IN () And if they return many rows, may want to use WHERE EXISTS.. Let us know On Thu, May 26, 2011 at 11:41 AM, Mark Atkinson ma...@aocs.org wrote: Hallo all, Forgive my SQL injection into this list - perhaps an answer to my question might also prove useful to others. I'm using three tables. There is no relationship between table1 and table3. I first query table1 for ordernumber and productcode, based on the productcode containing a certain string. Outputting the results, I query table2 for ordernumber and t2_ID based on table2.ordernumber matching table1.ordernumber. Looping those results, I query table3 for ID and Company based on table2.t2_ID. This, rather painfully I do admit, finds the table3.Company(ies) that has(have) purchased table1.productcode. Have Googled and read about how a single select with JOIN and/or UNION would achieve these results, but they also mention how I should essentially start at the end and work my way backwards, so to speak. Can't quite get my head around this. TIA for any advice. Mark ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344931 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
