Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    New York City
    Posts
    3

    Unanswered: mySQL Query Needed

    Greetings!

    I am new to dbforums, but think am eager to get involved. I am hoping to absorb some of the expertise that is offered here.

    My task at hand is the need to run a query of a mySQL database ... hoping somebody here can assist me with an appropriate query.

    I have the following three tables (amongst others):

    'outfitters'
    'outfitter_activities'
    'outfitter_destinations'

    Each record in the 'outfitters' table is to have at least one associated paired record from both the 'outfitter_activities' and 'outfitter_destinations' tables.

    What has happened is that the key field ('outfitterID') that links all three of the tables has been changed in the 'outfitter' table for a number of records, without having correspoding changes made to the records in the related tables. This has resulted in many 'orphaned' records in the '_activities' and '_destinations' tables.

    Most importantly, I would like to run a query that will show me all records in the 'outfitters' table that do not have corresponding records in the '_activities' and '_destinations' tables. Secondly, I would like to clean up the 'orphaned' records, so I would like to run a reverse query of the '_activities' and '_destinations' tables that will show me all records that do not have a related record in the 'outfitters' database. I hope this makes sense.

    I Use phpMyAdmin to access my database.

    Any help/suggestions would be greatly appreciated!

  2. #2
    Join Date
    Feb 2004
    Posts
    3

    Re: mySQL Query Needed

    Originally posted by WildSeeker
    Greetings!

    WS,

    I think what you need is an in clause ie.

    select *
    from outfitter_activities
    where OutfitterID not in (
    select OutfitterID
    from outfitters)

    variations on this should help to resolve your problems.


    I am new to dbforums, but think am eager to get involved. I am hoping to absorb some of the expertise that is offered here.

    My task at hand is the need to run a query of a mySQL database ... hoping somebody here can assist me with an appropriate query.

    I have the following three tables (amongst others):

    'outfitters'
    'outfitter_activities'
    'outfitter_destinations'

    Each record in the 'outfitters' table is to have at least one associated paired record from both the 'outfitter_activities' and 'outfitter_destinations' tables.

    What has happened is that the key field ('outfitterID') that links all three of the tables has been changed in the 'outfitter' table for a number of records, without having correspoding changes made to the records in the related tables. This has resulted in many 'orphaned' records in the '_activities' and '_destinations' tables.

    Most importantly, I would like to run a query that will show me all records in the 'outfitters' table that do not have corresponding records in the '_activities' and '_destinations' tables. Secondly, I would like to clean up the 'orphaned' records, so I would like to run a reverse query of the '_activities' and '_destinations' tables that will show me all records that do not have a related record in the 'outfitters' database. I hope this makes sense.

    I Use phpMyAdmin to access my database.

    Any help/suggestions would be greatly appreciated!

  3. #3
    Join Date
    Feb 2004
    Location
    New York City
    Posts
    3
    Thanks for the help!

    I actually ended determining that I needed something a bit different and found another query that really did the trick for me:

    SELECT email,company_name
    FROM outfitters
    LEFT JOIN `indexes` ON outfitters.email = indexes.outfitter_email
    WHERE indexes.outfitter_email IS NULL

Posting Permissions

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