Skip to main content
Known Participant
Sep 24, 2010 7:47 PM UTC
Question

<cfquery> and select statement

In my DB I have 2 tables. Employee_Info, Employee_Address.

I'm trying to retrieve information from the Employee_Info based on a condition in Employee_Address.

Employee_Info

ID               Name    EyeColor   Job_Status

111-1          Joe        black        Y

2124-2        Dan       brown        N

36789-1      Sam      brown        Y

Employee_Address

ID               Name     Address

111-1          Joe        102 W.Island Road

2124-2        Dan        345 N.Way Land

36789-1      Sam       8790 Yvonne Road

From my flex front end the user enters an ID like the following to retrieve information.

111 or 2124 or 36789.

Here is my Coldfuion code which is not returning anything...

<cfargument name="EmployeeNumber" type="string" required="no">

<cfquery name="getData" datasource="dsn">
      Select ID, Name, Address
          from Employee_Address EA
          where exists (select 1
                    from Employee_Info EI
                    where EI.Job_Status = 'Y'
                    and EI.ID = EA.ID)         

         <cfif isdefined ("arguments.EmployeeNumber")>
            and [ID] Like '#arguments.EmployeeNumber#' + '[-]%'
         </cfif>
         group by ID, Name, Address

</cfquery>

I do not understand whta is wrong with this.

Plese see if you can help... Any help is appreciated...

    This topic is closed to new replies. Start a new post to keep the conversation going.

    1 reply

    RosieGpAuthor
    Known Participant
    Sep 24, 2010 8:08 PM UTC

    i was putting in the wrong id...this works

    Translate