Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Jan 2004
    Posts
    26

    Question Unanswered: help with simple (or I thought) query

    Hello,

    I am trying to list results from a table where one column is equal and where the other column is different. I was thinking I could just use:

    select * from table where column1=column1 and column2<>column2

    but the results are not accurate (no reults returned)

    Any help would be greatly appreciated!

    Cheers!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ummm... Could you explain this in terms of the real world instead of referring to the table?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by limey
    select * from table where column1=column1 and column2<>column2
    column2 in a row can not have 2 values i.e. column2 is always equal to column 2 that is why you don't get any results.
    You might as wall say
    select * from table where 1=2
    It will never be true

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I'm just guessing here. Is it possible that you have a table with nearly duplicate rows, where multiple records have the same value for colum1 but with differences in the values in colum2?
    This is the way to locate those differences:
    Code:
    select T1.column1, T1.column2 as col2T1, T2.column2 as Col2T2
    from DaTable as T1 
       INNER JOIN DaTable as T2 ON
          T1.column1 = T1.column1
    WHERE T1.column2 <> T2.column2
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    How can column2 from one table not be equal to column2 of that same table ???

    It is impossible.

    Your query will never return records because of that faulty criteria.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by PracticalProgram View Post
    How can column2 from one table not be equal to column2 of that same table ???

    It is impossible.

    Your query will never return records because of that faulty criteria.
    Thank you for pointing out my typo. Here is the correct answer.
    Code:
    select T1.column1, T1.column2 as col2T1, T2.column2 as Col2T2
    from DaTable as T1 
       INNER JOIN DaTable as T2 ON
          T1.column1 = T2.column1
    WHERE T1.column2 <> T2.column2
    But you were referring to Column2. Am I missing something?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Aug 2009
    Posts
    262
    CREATE TABLE [dbo].[Table_1](
    [a] [int] NULL,
    [b] [nchar](10) NULL
    )


    select * from table_1

    a b
    ----------- ----------
    1 g
    1 y
    1 y
    1 h

    select T1.a, T1.b as col2T1, T2.b as Col2T2
    from Table_1 as T1
    INNER JOIN Table_1 as T2 ON
    T1.a = T2.a
    WHERE T1.b <> T2.b


    as suggested by wim

    ----Thank you for pointing out my typo. Here is the correct answer.
    ----Code:

    ----select T1.column1, T1.column2 as col2T1, T2.column2 as Col2T2
    ----from DaTable as T1
    ---- INNER JOIN DaTable as T2 ON
    ---- T1.column1 = T2.column1
    ---- WHERE T1.column2 <> T2.column2


    === RESULT========
    a col2T1 Col2T2
    ----------- ---------- ----------
    1 y g
    1 y g
    1 h g
    1 g y
    1 h y
    1 g y
    1 h y
    1 g h
    1 y h
    1 y h

    (10 row(s) affected)


    wow ...

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by PracticalProgram View Post
    It is impossible.
    That depends on what language you are using... It is possible in SQL.

    NULL.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Can you clarify ?

    If I am comparing a column to itself in a boolean test, like (column1<>column1), when would it ever be true?

    (1<>1) is false
    (2<>2) is false
    (3<>3) is false
    (Null<>Null) is also false, isn't it???
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...but so is (null=null) is false...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by PracticalProgram View Post
    Can you clarify ?

    If I am comparing a column to itself in a boolean test, like (column1<>column1), when would it ever be true?

    (1<>1) is false
    (2<>2) is false
    (3<>3) is false
    (Null<>Null) is also false, isn't it???
    Take a step back. Suppose we have two tables that both have a column with the same name. In the first table it could be a primary key, in the second it could be a foreign key.

    The join would look like:
    Code:
    ...
    FROM Table1 
         INNER JOIN Table2 ON
            Table1.column1 = Table2.column1
    Suppose there is another column name that occurs is both tables, and we want to find those records where the column1's are equal but the column2's are not equal.

    The join would look like:
    Code:
    ...
    FROM Table1
         INNER JOIN Table2 ON
            Table1.column1 = Table2.column1
    WHERE Table1.column2 <> Table2.column2
    Now suppose there are not two different tables, just one: Table1. And we want to know if there are records with the same column1 but with a different column2.

    The code that started this tread:
    Code:
    select * from table1 where column1=column1 and column2<>column2
    will never give a result, in one record 'column1=column1' will always be true and 'column2<>column2' can never be true, not even when there would be NULL values involved (NULL = NULL is always false and (indeed) NULL <> NULL is also always false)

    But you don't want to compare 1 record to itself, that is pretty useless. You want to compare different records. Most of the time they will also be from different tables, and then you write a JOIN between Table1 end Table2. Here we have a special case where Table1 is the same as Table2.

    You could try to write the join like:
    Code:
    ...
    FROM Table1
         INNER JOIN Table1 ON
            Table1.column1 = Table1.column1
    WHERE Table1.column2 <> Table1.column2
    But SQL Server will complain, because it won't know if 'Table1.column1 = Table1.column1' means 'Table1.column1 = Table1.column1' or 'Table1.column1 = Table1.column1' or 'Table1.column1 = Table1.column1' or ...

    You will have to specify what Table1 you are referring to, Table1 or Table1.

    This code will work:
    Code:
    ...
    FROM Table1 as T1
         INNER JOIN Table1 as T2 ON
            T1.column1 = T2.column1
    WHERE T1.column2 <> T2.column2
    You will join two different records (although coming from the same table) with the same value for their column1's. And now 'T1.column2 <> T2.column2' can potentially be true.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't believe null = null is false nor null <> null.
    They just aren't true

  13. #13
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    IF you are speaking of two tables, then this is a different story . . .

    . . . but the original question was framed around a single table . . .

    . . . my response speicifically talked about a single table . . .

    . . . Pat's response was specifically to my response about a single table . . .

    . . . so I questioned Pat's response about a single table.


    I want to solidify this . . .

    . . . if we are talking about a single table, there is NEVER a situation were the boolean test of (A<>A) is True.

    Isn't that correct?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Pat might have been being mischievous with you.

    "NULL <> NULL" will not evaluate to true. But you said:
    Quote Originally Posted by PracticalProgram View Post
    How can column2 from one table not be equal to column2 of that same table ???
    This is ever so slightly different. NULL can not be equal to NULL (in fact, it isn't). Tri-state logic.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Proof:
    Code:
    SELECT  'equal'
    WHERE   NULL = NULL
    
    SELECT  'not equal'
    WHERE   NULL <> NULL
    
    SELECT  'not equal'
    WHERE   NOT NULL = NULL
    
    SELECT  'equal'
    WHERE  NOT NULL <> NULL
    
    SELECT  'not equal'
    WHERE   NOT NOT NOT NOT NOT NOT NOT NOT NULL <> NULL
    Hence we have IS NULL

Posting Permissions

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