Pains of Subqueries

Results 1 to 3 of 3

Thread: Pains of Subqueries

  1. #1
    Michael Thompson Guest

    Default Pains of Subqueries

    SQL = "SELECT email<BR> FROM Contacts<BR> WHERE email = ANY ( <BR> SELECT email<BR> FROM Contacts<BR> GROUP BY email<BR> HAVING COUNT(email) &#062; 1)"<BR><BR><BR>It is supposed to return all of the records that have duplicate email addresses (i.e. the subquery finds the duplicate addys and the main query finds all of the records with those emails.)<BR><BR>Help would be great.<BR>Thank You

  2. #2
    Join Date
    Dec 1969

    Default RE: Pains of Subqueries

    Why even do the outer Query???<BR><BR>Won&#039t the Subquery give oyu what you are looking for?<BR><BR>SELECT email FROM Contacts GROUP BY email HAVING COUNT(email) &#062; 1<BR><BR>Should give you a list of all the email address that are duplicated.<BR><BR>If you want a list of all duplicate email address (I mean both records), try...<BR><BR>SQL = "SELECT email<BR>FROM Contacts<BR>WHERE email IN ( <BR>SELECT email<BR>FROM Contacts<BR>GROUP BY email<BR>HAVING COUNT(email) &#062; 1)"<BR><BR><BR>HTH<BR><BR>Jerry<BR><BR><BR>

  3. #3
    Michael Thompson Guest

    Default Already tried that

    Unfortunately that does not work, it is apparently some kind of bug. Anyhow, this works:<BR><BR>SELECT entryid, email, name, companyid<BR>FROM Contacts CMain<BR>WHERE EXISTS (<BR> SELECT email<BR> FROM Contacts CSub <BR> WHERE = <BR> GROUP BY email <BR> HAVING COUNT(email) &#062; 1)<BR><BR>Thanks for the suggestion though

Posting Permissions

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