Skip to main content
5
539E
Inspiring
March 17, 2008
Answered

SQL help

  • March 17, 2008
  • 1 reply
  • 366 views
Hi. i've been out of the loop for awile & trying to write this query & it's not workin.
Can someone please help this forgetful soul. ha

tables are: names & orders.
I'm selecting stuff from orders & I want it to choose orders.repemail = '#url.e#' and where names.company = '#session.company#'

The common link in both is names.repemail & orders.repemail

Guidance, please...inner join, outer join, join on, multiple selects...i'm so frustrated right now.
    This topic has been closed for replies.
    Correct answer 539E
    I was gettin close, but I just had JOIN, & put a ' ' around = 'O.repemail' ..oh well, guess it didn't matter it wasn't a number.

    I needed to define the select clause because it said that repemail could be found in more than one table.

    select O.repemail, O.orderdate, O.invoice, O.descr, O.amount, O.customer from orders O
    INNER JOIN names ON names.repemail = O.repemail
    WHERE O.repemail = <cfqueryparam value="#url.e#">
    AND names.company = <cfqueryparam value="#session.company#"> order by O.orderdate DESC

    & I'm glad it stays under the company so the url can't be changed & shown output for another company.
    That was also a problem I had.

    Thanks for your help!

    1 reply

    N
    Newsgroup_User
    Inspiring
    March 17, 2008
    539E wrote:
    > Hi. i've been out of the loop for awile & trying to write this query & it's not
    > workin.
    > Can someone please help this forgetful soul. ha
    >
    > tables are: names & orders.
    > I'm selecting stuff from orders & I want it to choose orders.repemail =
    > '#url.e#' and where names.company = '#session.company#'
    >
    > The common link in both is names.repemail & orders.repemail
    >
    > Guidance, please...inner join, outer join, join on, multiple selects...i'm so
    > frustrated right now.
    >
    >


    Sounds like an inner join to me.

    SELECT
    stuff

    FROM
    orders INNER JOIN names ON orders.repemail = names.repemail

    WHERE
    orders.repmail = <cfqueryparam value="#url.e#" cf_sql_type="..."> AND
    names.company = <cfqueryparam value="#session.company#"
    cf_sql_type="...">

    5
    539EAuthorCorrect answer
    Inspiring
    March 17, 2008
    I was gettin close, but I just had JOIN, & put a ' ' around = 'O.repemail' ..oh well, guess it didn't matter it wasn't a number.

    I needed to define the select clause because it said that repemail could be found in more than one table.

    select O.repemail, O.orderdate, O.invoice, O.descr, O.amount, O.customer from orders O
    INNER JOIN names ON names.repemail = O.repemail
    WHERE O.repemail = <cfqueryparam value="#url.e#">
    AND names.company = <cfqueryparam value="#session.company#"> order by O.orderdate DESC

    & I'm glad it stays under the company so the url can't be changed & shown output for another company.
    That was also a problem I had.

    Thanks for your help!
    -
    -__cfSearching__-
    Inspiring
    March 17, 2008
    539E,

    In case that is your real code, do not forget to include the cfsqltype like in Ian's example. Use whatever cfsqltype corresponds to your column data types. Examples are cf_sql_varchar, cf_sql_integer, etcetera.
    http://livedocs.adobe.com/coldfusion/7/htmldocs/00000317.htm

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices