Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004

    Question Unanswered: SQL statement "not in"

    I have a couple tables and I need to compare some information. If the two columns I am comparing are the same length and I use the "not in" statement, everything is fine, but if they differ in length it won't work for some reason. Any ideas?
    Here is the code I'm using:

    select a.SOP from labor as a
    where a.SOP not in (select b.tSOP from torch as b);

    This works fine when column SOP and tSOP have the same amount of fields, but not if SOP if shorter than tSOP.

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    I think you really want
       FROM labor as a
       WHERE NOT EXISTS (select *
          from torch as b
          WHERE b.tSOP = a.SOP);

  3. #3
    Join Date
    Oct 2003
    I don't know the exact answer to your question in the exact context you're referring to... but I do observe that there might be a better way to solve that query.
    SELECT sop FROM labor a 
    LEFT JOIN torch b USING (a.sop = b.tsop)
    WHERE b.tsop IS NULL
    (extemporaneous coding... use only as directed.)
    ChimneySweep(R): fast, automatic
    table repair at a click of the

  4. #4
    Join Date
    Jun 2003
    Provided Answers: 1
    Confusing post. Your first paragraph talks about the problem occuring when the two fields are not the same lenghth. Your last paragraph says the problem occurs when the columns don't have the "same amount of fields". I'm going to assume that is a misprint, and that your problem is that the two columns are different lengths.

    Given columnA varchar(10) and columnB varchar(20), any values in the two tables can be directly compared as long as the value in columnB is less than or equal to 10 characters. If it is 11 characters long, it can never match a value from columnA.

    You should start by rewriting your statement like this to take advantage of any indexing on the columns.

    select labor.SOP
    from labor
    left outer join torch on labor.SOP = torch.tSOP
    where torch.tSOP is null

    This will find EXACT MATCHES, based on length and characters, ignoring any trailing spaces (for char columns). If you want columnA varchar(10) against the first 10 characters of columnB varchar(20), use this:

    select labor.SOP
    from labor
    left outer join torch on labor.SOP = left(torch.tSOP, 10)
    where torch.tSOP is null

    ..but you will lose the benefits of any index on torch.tSOP.

    Another general solution, not knowing the lengths of labor.SOP or torch.tSOP, is this:

    select labor.SOP
    from labor
    left outer join torch on labor.SOP like 'torch.tSOP%'
    where torch.tSOP is null

    I hope one of these posts has been helpful, and if not then please post again and explain your problem more clearly.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts