You could pull the max ID from each table like this:
<CFQUERY NAME="GetNumber" datasource="OIMPROD">
> SELECT Max(db2admin.tb_assigned.id) AS MaxOfID1,
Max(db2admin.tb_complete.id) AS MaxOfID2
> FROM db2admin.tb_assigned,db2admin.tb_complete
> where db2admin.tb_assigned.id = db2admin.tb_complete.id
> </cfquery>
>
> <CFSET NextPONum = getnumber.MaxOfID1 + 1> or whatever...
But, I'm unclear as to the relationship between the tables. Will both tables
necessarily always have the same number of ID's? If so, you can query on
just one of ID fields instead of both...what exactly are you looking to do?
T
----Original Message Follows----
From: "James Taavon" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: SQL JOIN
Date: Fri, 25 Aug 2000 10:59:12 -0400
The thing is I need am pulling the ID field from both tables. So should
I just rename one column a differnet name?
Terri Stocke wrote:
>
> You need to qualify which table you are pulling the id from, since you
are
> using two different tables in your query...
>
> <CFQUERY NAME="GetNumber" datasource="OIMPROD">
> SELECT Max(tablename.id) AS MaxOfID
> FROM db2admin.tb_assigned,db2admin.tb_complete
> where db2admin.tb_assigned.id = db2admin.tb_complete.id
> </cfquery>
>
> <CFSET NextPONum = getnumber.MaxOfID + 1>
>
> ----Original Message Follows----
> From: "James Taavon" <[EMAIL PROTECTED]>
> Reply-To: [EMAIL PROTECTED]
> To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
> Subject: SQL JOIN
> Date: Fri, 25 Aug 2000 10:44:41 -0400
>
> Hello.
>
> I am evaluating two columns under the same name in two different tables.
> The error I got, "A reference to column "ID" is ambiguous" was expected.
> My code is below. Can I do and INNER JOIN two resolve this error and
> retrieve the correct value? If so , how? I don't have much practice with
> using JOIN.
>
> Thanks in advance!
>
> <CFQUERY NAME="GetNumber" datasource="OIMPROD">
> SELECT Max(id) AS MaxOfID
> FROM db2admin.tb_assigned,db2admin.tb_complete
> where db2admin.tb_assigned.id = db2admin.tb_complete.id
> </cfquery>
>
> <CFSET NextPONum = getnumber.MaxOfID + 1>
>
> <cfoutput query="GetNumber">
> #NextPONum#
> </cfoutput>
>
------------------------------------------------------------------------------
> Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> the body.
>
> ________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
>
>
------------------------------------------------------------------------------
> Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
