SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Quake 1 Addict CreedFeed's Avatar
    Join Date
    Feb 2002
    Location
    Milwaukee, WI
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select rows from same table which have different values in a field

    Lets say I have a table with three columns:

    id name field
    1 test123 abc
    2 test456 abc
    3 test123 def
    4 test456 abc
    5 test789 abc

    I want to select all "names" from the table which appear more than once and in which the field is not the same. In the above example, I should get one result back - the name "test123". How would I do that?
    -- Steve Caponetto
    Quake 1 Resurrection :: CreedFeed

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Code:
    select
    name, field
    from
    tablename
    group by
    name, field
    This will return a result set of unique rows between the two columns. Now lets query this data set to see if there are duplicates of name...

    Code:
    SELECT t1.name
    FROM (select
    name, field
    from
    table1
    group by
    name, field
    )  AS t1
    GROUP BY t1.name
    HAVING Count(t1.name)>1

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT name
      FROM daTable
    GROUP
        BY name
    HAVING COUNT(DISTINCT field) > 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Quake 1 Addict CreedFeed's Avatar
    Join Date
    Feb 2002
    Location
    Milwaukee, WI
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Awesome. Thank you both. I was originally trying to write something like what K. Wolfe posted, but it looks like r937's is cleaner. I always forget about using DISTINCT within the function like that.

    One further question - is there a better way to select all "names" and "fields" from this result set, other than doing this:

    Code:
    SELECT t2.name, t2.field
      FROM (
          SELECT name
            FROM daTable
          GROUP
              BY name
          HAVING COUNT(DISTINCT field) > 1
    ) AS t1
    LEFT JOIN daTable AS t2 ON t1.name = t2.name
    -- Steve Caponetto
    Quake 1 Resurrection :: CreedFeed

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by CreedFeed View Post
    ... is there a better way to select all "names" and "fields" from this result set, other than doing this:
    yes -- INNER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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