Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: finding duplicates - is this right?

    I've done a search and I THINK I've got my head round this, but I'd be very grateful if someone could reassure me:

    Code:
    SELECT Email FROM List1 WHERE EXISTS (
    	SELECT Email FROM List2 WHERE List2.Email= List1.Email
    ) AND List1.Email <> '44'
    That will give me every email address from list one that (a) appears in list two, and (b) isn't '44'. Right?

    And to find all the emails from List1 that DON'T occur in List2 (and aren't '44'), I just put "NOT" in front of "EXISTS". Right?

    Sorry for asking an obvious question but I'm having a real mental block here.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yep, that should give you the results you want!
    Here's my 2 stabs at the problem - please correct me if I'm wrong.
    Code:
    SELECT	Email
    FROM	List1 
    WHERE	Email IN
    	(
    	SELECT	Email
    	FROM	List2
    	)
    AND	Email <> '44'
    
    
    SELECT	Email
    FROM	List1
    INNER JOIN List2
    	ON List1.Email <> List2.Email
    WHERE	List1.Email <> '44'
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    please correct me if I'm wrong.
    second one is wrong, it's almost a complete cross join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh sorry! I see why it's wrong now - silly me
    Thanks Rudy ^_^
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2002
    Posts
    189
    Thank you

    Regarding your first stab - that's what I had for finding the duplicates, but when I tried using that to find the non-duplicates (by sticking "NOT" in front of "IN"), I get no rows returned. I'm still at a loss as to why that would happen - does "NOT" not work with "IN"?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Spudhead
    does "NOT" not work with "IN"?
    no, it does
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try these methods instead:
    Code:
    select	distinct List1.Email
    from	List1
    	inner join List2 on List1.Email = List2.Email
    where	List1.Email <> '44'
    
    select	distinct List1.Email
    from	List1
    	left outer join List2 on List1.Email = List2.Email
    where	List1.Email <> '44'
    	and List2.Email is null
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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