mySQL Query Needed 1

[WildSeeker]

Greetings!

I am new to tek-tips, and am eager to get involved here. 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!

Regards,
Tim

http://www.wildseeker.com

The Adventure Travel Resource

 

[sleipnir214]

I suggest you use a left join.

SELECT * from
outfitters o
left join
outfitter_activities oa
on
o.outfitterid = oa.outfitterid
where
oa.outfitterid is null

this query returns every record in outfitters (the table on the left-hand side of the left join operator) and the related record from outfitter_activities, even if there is no related record in outfitter_activities. A simple join would only return that data for which there are corresponding records in both tables.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[WildSeeker]

Hello there ...

Sorry for my delay in responding to your post sleipnir ... I got distracted from this project for a bit.

Actually, I had incorrectly referenced the related table in my initial post that the 'outfitters' table is related to. It is not 'outfitters_activities', but rather 'indexes' .... yes, there is actually a table called 'indexes'.

This has caused me difficulties in using the query that you have suggested, as phpMyAdmin reades the table name 'Indexes' as an operator and returns a message that I am using incorrect syntax in my query.

Basically, what I need to do is determine that records in the 'outfitters' table that do not have an associated record in the 'indexes' table. Here is the query that I used:

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

Any ideas how I can accomplish this?

Regards,
Tim

http://www.wildseeker.com

The Adventure Travel Resource

 

[sleipnir214]

That's weird. According to the page on MySQL reserved words, "INDEXES" is not a reserved word.

You might try putting indexes inside of backticks"

left join `indexes`....



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[WildSeeker]

That was the trick ... thanks. You really helped me out a lot!!

Regards,
Tim

http://www.wildseeker.com

The Adventure Travel Resource