find redundant records

[homerUK]

Hi,

I need an SQL statement to find (and eventually) delete and redundant records in my database.

The structure is

TABLE: Survey
surveyID
etc etc


TABLE: Responses
responseID
surveyID
etc etc

So I need to find all the records in responses that DO NOT have a surveyID which is valid. Ie: Any records which have a surveyID that does not exist any longer.

I have done this


SELECT
s.surveyID as survey_surveyID,
r.surveyID as repsonse_surveyID
FROM survey s
left outer join responses r on
r.surveyID != s.surveyID


but it seems to be bringing back records which have a valid survey. Ie: The survey still exists in the survey table.

Thanks for any help!

[raf]

what mysql version are you running?

[homerUK]

im running 4.0.0 :)

[raf]

im running 4.0.0 :)
so you can't use a subselect then.

you'll best use an outerjoin like this

SELECT
s.surveyID as survey_surveyID,
r.surveyID as repsonse_surveyID
FROM responses r LEFT JOIN survey s ON r.surveyID = s.surveyID
WHERE s.surveyID IS NULL

[homerUK]

hi raf - that works to show all the NULL records, of which there are a few.. but I need to find out which records in the responses table are tied to a surveyID which doesnt exist any more.

the problem I have is the old code - when you deleted a survey, it didnt delete the responses associated with it. I now need to remove these!!

thanks :)

[SpirtOfGrandeur]

SQL:
SELECT *
FROM Responses
WHERE NOT EXISTS
(SELECT surveyID
FROM Survey
WHERE Responses.surveyID = Survey.SurveyID)

[guelphdad]

SpirtOfGrandeur: That doesn't work for this user. If you look above they are using 4.0 which doesn't support subqueries.

homerUK, the results returned by raf's query are those where the right hand table is null, in other words they don't exist in your survey table but do in your responses table so they are the ones you want to delete.

[SpirtOfGrandeur]

SpirtOfGrandeur: That doesn't work for this user. If you look above they are using 4.0 which doesn't support subqueries.

Good point. I did not read the version number, then again I have never used mySQL and I just threw that together off the top of my head in SQL logic.

[homerUK]

thanks for the replies...
ive tried Raf's code... but I'm not sure its what I need.
eg: I can query the database, and there are responses in the table with the surveyID 8. When I go to the surveys table, there is NO survey with the ID 8. These are the ones I want to get rid of.

thanks for all the help guys :)