SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm trying the clean up an old and poorly maintained database and need some help writing some queries to help make the job easier. Let's say I have the following two tables.

    Company
    ------------------
    ID
    company_name


    Contact
    -------------------
    ID
    contact_name
    company_name
    company_ID


    First I want to add all the company_name in the Contact table to the the company_name in the Company table that are not all ready listed. I than want to do a check that to make sure that the company_ID and company_name in Contact table match the ID and company_name in the the Company table. Finally, I would like to run a delete in Company table where there isn't a company_ID in Contact to match the ID in Company. I hope all of that is clear enough.
    Is any of this possible?

    Thanks

  2. #2
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    anything is possible but it makes no sense IMHO.

    You should have tables like this

    Company
    ID
    Company_name

    and then

    Contact
    ID
    Company_Id
    Contact_Name

    assuming that each contact only works for one company. If they don't then you should do:

    Company

    ID
    Company_name


    Contact

    ID
    Contact_Name


    Contact_Company
    Contact_ID
    Company_ID

    if you don't think so, let us know what you are trying to do a bit more. What you want to do does not really make much sense as far as i can see.

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with Padders.

    His way helps eliminate duplicate data. The need to check data to make sure that a field in table X matches a field in table Y goes away. To top it off its fast .

    So if you can re-do all the tables, I would go with his suggestion.

    Just my $0.02.

    BlackCatt

  4. #4
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I plan on dropping the the Contact.company.name field as the last step in all the queries. I didn't ask how to to do that because it is so easy to to do.

    This database was designed and maintain by someone didn't know the first thing about working with databases much less about how to design one (feel my pain). This is why I'm trying to clean up the database. The person who maintain this DB keep changing his mind on how he want to store and organize the information. As a result some Contact entries only only a company_name and not a company_ID. Some entries have a company_ID and not a company_name field. Not all companies are listed the the Company table. And if that wasn't bad enough, some entries in the Contact table have neither a company_name or a company_ID as this information is was placed in a completely different table. Again feel my pain.

    I hope this clears some things up.

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    CyberFuture,
    You have my condolences.
    All I can say is... Have fun with that!

    BlackCatt

  6. #6
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still looking for help


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
  •