SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict Viral's Avatar
    Join Date
    Nov 2001
    Location
    Washington DC
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    find orphaned records w/ join

    Guys,

    I'm going to be working on a project that will have huge tables (several million records each). One of my fears is that, over time, some of the records in the various tables might become 'orphaned' so that they are no longer needed. Here's an example:

    Table1:
    id | name
    ----------------
    1 | David
    2 | John
    3 | Bill
    4 | Kristy

    Table2:
    id | dept_id
    ----------------
    1 | 1234
    2 | 2345
    3 | 3456
    4 | 4321

    lookup:
    name_id | dept_id
    -----------------
    1 | 2
    2 | 3
    2 | 4
    3 | 4
    4 | 4

    In this example, dept 1234 no longer has any employees asociated with it. It's orphaned. My fear is that, as large as these tables will be, I'm wondering if there will be an effecient way to clean up these tables. Is there an equivalent SQL query for mysql that works like this:

    delete from table2 where id NOT IN (select dept_id from lookup group by dept_id)

    Thanks,
    --Viral
    A computer without Windows is like a chocolate cake without mustard.

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1. Isn't an orphan a child without parents? If a department has no employees, it's not orphaned (But if an employee has no department, then the employee is orphaned)

    2. If you want a department to be deleted when the last employee is moved or deleted from the department, write code for this event (don't "clean up" later)

    3. If you want to clean up later anyway, use a query like this (copied from MySql Docs):
    select concat('delete from table2 where id = ', t2.id)
    from table2 t2 left join lookup l on t2.id = l.dept_id
    where l.dept_id is null
    Then execute each row returned to delete the depts

  3. #3
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you're going to be dealing with millions of records, then you need to use a decent DB engine like SQL Server 2000 or Oracle, in which case you can set triggers, relationships, and constraints to ensure that orphans are never created. That's exactly why they were invented!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Or wait for MySql 5.0
    The planned update language will be able to handle stored procedures. Our aim is to have stored procedures implemented in MySQL Server around version 5.0. We are also looking at triggers.


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
  •