SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    deleting the garbage

    Hey,

    When I do SELECT COUNT(id) FROM customers it returns 1, 543. But When I JOIN the customers table with a 2nd table that references the IDs the number is actually higher.

    Here is the query:

    Code MySQL:
    SELECT COUNT(id)
    FROM customers
    INNER JOIN favs
      ON favs.userID = customers.id

    So what I'm trying to do is show the IDs that are no longer in the customers table so that I can delete them and the COUNT can be accurate.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If a user has 5 favs, there will be 5 rows for that user here. You are not counting distinct customers but how many customer-favorite pairs there are. That's why the number is higher.

    An INNER JOIN won't ever return rows from one table that don't have corresponding rows in the other.

  3. #3
    Employed Again Viflux's Avatar
    Join Date
    May 2003
    Location
    London, On.
    Posts
    1,127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    Code SQL:
     
    DELETE FROM C
    FROM customers AS C
      LEFT JOIN favs AS F ON F.userID = C.id
    WHERE F.userID IS NULL
    That should remove all the records from the "customers" table that don't have a match in the "favs" table. Change the "DELETE FROM C" line to "SELECT" to see the records.


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
  •