Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

"not in" sub query not working

Discussion in 'Information Technology' started by faulknerandy@yahoo.com, Jul 28, 2006.

  1. faulknerandy@yahoo.com

    faulknerandy@yahoo.com
    Expand Collapse
    Guest

    Hi,

    I have a SQL written query that looks like this:

    SELECT tblNominal.code
    FROM tblNominal
    WHERE (((tblNominal.code) Not In (select txtCode from
    tblReportGroup)));

    As far as I understand things this should list codes from the
    tblNominal table that do not appear in the list from the tblReportGroup
    table...only it doesn't work!

    The query returns nothing, despite the fact that I know for certain
    there are rows in tblNominal containing codes that are not in the sub
    query.

    Can anybody help?

    I'm using Access 2003.

    Alfie
     
  2. Loading...

    Similar Threads Forum Date
    Movies Want movie Subeg Singh Shahbaz Singh Theatre, Movies & Cinema Jan 12, 2016
    United Sikhs Sikh woman sues suburban go-kart park after hair cut from motor Sikh Organisations Jan 9, 2014
    World Milkha Singh-inspired character makes appearance on Subway Surfers Breaking News Jan 7, 2014
    The Inner Experience and the Subconscious Mind Interfaith Dialogues Dec 6, 2013
    Opinion 1984 Sikh Genocide Petition To Be Submitted To The UN Breaking News Oct 23, 2013

  3. ChrisM

    ChrisM
    Expand Collapse
    Guest

    That should work as you describe.

    Is the data type and formatting of the two fields identical? Have you got
    leading zeros, or trailing spaces or some such thing in one or other of the
    tables?
    Try making 2 temp tables with a subset of the data in the two actual tables,
    and experiment on those. It should be a lot easier to spot the problem if
    you only have a few records in each table...

    Cheers,

    Chris.

    <faulknerandy@yahoo.com> wrote in message
    news:1152882229.837484.226160@i42g2000cwa.googlegroups.com...
    > Hi,
    >
    > I have a SQL written query that looks like this:
    >
    > SELECT tblNominal.code
    > FROM tblNominal
    > WHERE (((tblNominal.code) Not In (select txtCode from
    > tblReportGroup)));
    >
    > As far as I understand things this should list codes from the
    > tblNominal table that do not appear in the list from the tblReportGroup
    > table...only it doesn't work!
    >
    > The query returns nothing, despite the fact that I know for certain
    > there are rows in tblNominal containing codes that are not in the sub
    > query.
    >
    > Can anybody help?
    >
    > I'm using Access 2003.
    >
    > Alfie
    >
     
  4. Sylvain Lafontaine

    Sylvain Lafontaine
    Expand Collapse
    Guest

    One possibility would be that one of the txtCode is a Null value. Try
    adding the condition "Where txtCode is Not Null" in the subquery.

    --
    Sylvain Lafontaine, ing.
    MVP - Technologies Virtual-PC
    E-mail: http://cerbermail.com/?QugbLEWINF


    <faulknerandy@yahoo.com> wrote in message
    news:1152882229.837484.226160@i42g2000cwa.googlegroups.com...
    > Hi,
    >
    > I have a SQL written query that looks like this:
    >
    > SELECT tblNominal.code
    > FROM tblNominal
    > WHERE (((tblNominal.code) Not In (select txtCode from
    > tblReportGroup)));
    >
    > As far as I understand things this should list codes from the
    > tblNominal table that do not appear in the list from the tblReportGroup
    > table...only it doesn't work!
    >
    > The query returns nothing, despite the fact that I know for certain
    > there are rows in tblNominal containing codes that are not in the sub
    > query.
    >
    > Can anybody help?
    >
    > I'm using Access 2003.
    >
    > Alfie
    >
     
  5. Graham R Seach

    Graham R Seach
    Expand Collapse
    Guest

    Andy,

    IN and NOT IN are notoriously slow in Jet. You'll get better performance by
    using a frustrated join.

    SELECT tblNominal.code
    FROM tblNominal LEFT JOIN tblReportGroup
    ON tblNominal.code = tblReportGroup.txtCode
    WHERE tblReportGroup.txtCode Is Null

    Regards,
    Graham R Seach
    Microsoft Access MVP
    Sydney, Australia
    ---------------------------

    <faulknerandy@yahoo.com> wrote in message
    news:1152882229.837484.226160@i42g2000cwa.googlegroups.com...
    > Hi,
    >
    > I have a SQL written query that looks like this:
    >
    > SELECT tblNominal.code
    > FROM tblNominal
    > WHERE (((tblNominal.code) Not In (select txtCode from
    > tblReportGroup)));
    >
    > As far as I understand things this should list codes from the
    > tblNominal table that do not appear in the list from the tblReportGroup
    > table...only it doesn't work!
    >
    > The query returns nothing, despite the fact that I know for certain
    > there are rows in tblNominal containing codes that are not in the sub
    > query.
    >
    > Can anybody help?
    >
    > I'm using Access 2003.
    >
    > Alfie
    >
     
  6. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Or Exists joins...

    Pieter

    "Graham R Seach" <gseach@accessmvp_REMOVE.com> wrote in message
    news:uF5rs30pGHA.3908@TK2MSFTNGP05.phx.gbl...
    > Andy,
    >
    > IN and NOT IN are notoriously slow in Jet. You'll get better performance
    > by using a frustrated join.
    >
    > SELECT tblNominal.code
    > FROM tblNominal LEFT JOIN tblReportGroup
    > ON tblNominal.code = tblReportGroup.txtCode
    > WHERE tblReportGroup.txtCode Is Null
    >
    > Regards,
    > Graham R Seach
    > Microsoft Access MVP
    > Sydney, Australia
    > ---------------------------
    >
    > <faulknerandy@yahoo.com> wrote in message
    > news:1152882229.837484.226160@i42g2000cwa.googlegroups.com...
    >> Hi,
    >>
    >> I have a SQL written query that looks like this:
    >>
    >> SELECT tblNominal.code
    >> FROM tblNominal
    >> WHERE (((tblNominal.code) Not In (select txtCode from
    >> tblReportGroup)));
    >>
    >> As far as I understand things this should list codes from the
    >> tblNominal table that do not appear in the list from the tblReportGroup
    >> table...only it doesn't work!
    >>
    >> The query returns nothing, despite the fact that I know for certain
    >> there are rows in tblNominal containing codes that are not in the sub
    >> query.
    >>
    >> Can anybody help?
    >>
    >> I'm using Access 2003.
    >>
    >> Alfie
    >>

    >
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4231 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  7. ChrisM

    ChrisM
    Expand Collapse
    Guest

    So, what is best:

    (1)
    SELECT SomeId FROM TableA
    WHERE NOT EXISTS
    (
    SELECT * FROM TableB
    WHERE TableB.Code = TableA.SomeId
    )

    OR

    (2)
    SELECT someId
    FROM TableA LEFT JOIN TableB
    ON TableA.SomeId = TableB.Code
    WHERE TableB.Code Is Null

    OR

    (3)
    SELECT someId
    FROM TableA
    WHERE someId NOT IN
    (
    SELECT Code FROM TableB
    )

    It seems, from a previous post that the third option is the least efficient
    (though possibly the easiest to understand)

    How about the other two? Is either one preferred over the other? Which is
    faster? Or does it depend on the amount of data that you have?

    Chris.



    "Pieter Wijnen"
    <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
    wrote in message news:eZaRz90pGHA.4812@TK2MSFTNGP04.phx.gbl...
    > Or Exists joins...
    >
    > Pieter
    >
    > "Graham R Seach" <gseach@accessmvp_REMOVE.com> wrote in message
    > news:uF5rs30pGHA.3908@TK2MSFTNGP05.phx.gbl...
    >> Andy,
    >>
    >> IN and NOT IN are notoriously slow in Jet. You'll get better performance
    >> by using a frustrated join.
    >>
    >> SELECT tblNominal.code
    >> FROM tblNominal LEFT JOIN tblReportGroup
    >> ON tblNominal.code = tblReportGroup.txtCode
    >> WHERE tblReportGroup.txtCode Is Null
    >>
    >> Regards,
    >> Graham R Seach
    >> Microsoft Access MVP
    >> Sydney, Australia
    >> ---------------------------
    >>
    >> <faulknerandy@yahoo.com> wrote in message
    >> news:1152882229.837484.226160@i42g2000cwa.googlegroups.com...
    >>> Hi,
    >>>
    >>> I have a SQL written query that looks like this:
    >>>
    >>> SELECT tblNominal.code
    >>> FROM tblNominal
    >>> WHERE (((tblNominal.code) Not In (select txtCode from
    >>> tblReportGroup)));
    >>>
    >>> As far as I understand things this should list codes from the
    >>> tblNominal table that do not appear in the list from the tblReportGroup
    >>> table...only it doesn't work!
    >>>
    >>> The query returns nothing, despite the fact that I know for certain
    >>> there are rows in tblNominal containing codes that are not in the sub
    >>> query.
    >>>
    >>> Can anybody help?
    >>>
    >>> I'm using Access 2003.
    >>>
    >>> Alfie
    >>>

    >>
    >>

    >
    >
    >
    > --
    > ----------------------------------------
    > I am using the free version of SPAMfighter for private users.
    > It has removed 4231 spam emails to date.
    > Paying users do not have this message in their emails.
    > Get the free SPAMfighter here: http://www.spamfighter.com/len
    >
    >
     

Share This Page