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 <[email protected]> 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
