The ID field is the number assigned to a certain project. This value
will exist in both tables, but not at the same time. To assign a new
number to a project I need to check the "Current Assignment" table ID
and the "Complete Assignment" ID to make sure that I dont re-assign a
number already used. If the number already exists in the complete table
without my knowledge, then when the "current assignment" table ID gets
passed into the "complete assignment" table ID will result in the
standard SQL error referring to duplicate row values. The goal is to
have this number generated automatically and inserted into the form so I
or anybody else does have to figure out what the last number used was
for a project.
Does that make sense?
Terri Stocke wrote:
>
> 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/[email protected]/
> > 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/[email protected]/
> > 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/[email protected]/
> 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/[email protected]/
> 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/[email protected]/
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.
