Skip to main content
A
Anonymous
September 19, 2011
Answered

Using NOT Exists in a query

  • September 19, 2011
  • 4 replies
  • 3810 views

There has to be something simple that I'm missing.....

One table is being used

I need to see who registered last season but has not registerd for this season.  Each time they register for a new season, it creates a new record.

I have this

<cfquery name="doh" datasource=#datasource#>

          select * from data

              where season='#prev1.season#' and  NOT EXISTS (season='#cur1.season#')

  </cfquery>

seems simple enough..but receiveing an incorrect syntax error.   This is simplying asking for all records that played last year, 2010-2011 but there is not record of them for this year 2011-2012 meaning they haven't registered.

any and all help is GREATLY appreciated.

Sharon

    This topic has been closed for replies.
    Correct answer Dan_Bracuk

    This looks good in theory but in practice it will be very slow.  A quicker way would be:

    select JustTheFieldsYouNeed

    from data

    where season = #currentseason#

    and UserId in

    (

    select UserId

    from data

    where season = #currentseason#

    except

    select UserId

    from data

    where season = #previousseason#

    )

    Note that the keyword "except" is not supported by all dbs.  Sometimes you have to use the word "minus".

    4 replies

    A
    Anonymous
    September 20, 2011

    Than you everyone for all of your help.  I was unable to use 12 Robots suggestion as each entry gets a new user id.  Way it was written about 10 ys ago..however.  Dan....Yours worked perfect for me.  I was able to pull all the records I needed!  Thank you!!!!

    A
    Anonymous
    September 19, 2011

    Getting better

    Using this query

    <cfquery name="doh" datasource=#datasource#>

                select * from data

                  where season='#prev1.season#' and NOT season=(select season from data where season='#cur1.season#')

      </cfquery>

    Returning Error   Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    J
    JMF3
    Participating Frequently
    September 19, 2011

    select * from data

    where season='#prev1.season#' and season NOT IN (select

    season from data where season='#cur1.season#')

    </cfquery

    A
    Anonymous
    September 20, 2011

    JMF  

    At least this doesn't error. but it returns the wrong record count. 

    the previous season has 1098 records.  the current season has 698.  I need to pull the ones that registerd last season but not this.  Your query pulls the previous record count. 

    12Robots
    12Robots
    Participating Frequently
    September 19, 2011

    I am thinking somethign like this. Note, I did nto test this, so it may have issues. But I do think that using a subquery is going to be your way to go.

    Also, don't forget your <cfqueryparams />, especially if this is user-provided data.

    <cfquery name="doh" datasource=#datasource#>

              select * from data d1

                  where d1.season='#prev1.season#' and  NOT d1.season = (SELECT season from data d2 where d1.id = d2.id AND session = #cur1.season#)

      </cfquery>

    A
    Anonymous
    September 19, 2011

    12 robots.  I am quite clueless when it comes to Sub queries....for example....select * from data d1  what is d1 referring to? 

    12Robots
    12Robots
    Participating Frequently
    September 19, 2011

    d1 is an alias for the table "data".  So hence forth in the query I can refer to that table as d1. d2 is another refernece to that "data" table, but from another query (the subquery). That allows me to refernece the same table (data) from two different queries.

    R
    Reed_Powell-ttnmOb
    Inspiring
    September 19, 2011

    EXISTS and NOT EXISTS reference the subquery that follows them in parens.  You do not have a valid SQL SELECT statement in the subquery.  you probably want something like

    where season='#prev1.season#' and  NOT EXISTS ( SELECT season from data where season='#cur1.season#')

    A
    Anonymous
    September 19, 2011

    Reed  I had tried that also...got a 0 record count returned

    R
    Reed_Powell-ttnmOb
    Inspiring
    September 19, 2011

    Please post the sql that returned the 0 records but did not work.

    What happens if you execute that subquery by itself, what do you get back?

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