X Cart database redundancy problem, annoying Qualitive team error in database

[j.smith1981]

This has to be the worst ever database design I have ever seen in my entire life of doing work with databases.

 

I have a product store, with categories and sub categories.

 

Just leaving it at the root categories for now.

 

There's 2 tables thats causing the problem (cant rejig it, as that would take forever to make it work properly, ie cant restructure the database unfortunately).

 

Here's the structure basically for it (making a lite version here so I can amend to my own usage!):

 

xcart categories:

 

ID      |    Categoryname

1271        Test Category

 

But then when I add this in as a category, a new category of course appears in the customer front end, but not coming up with 'Test Category'

 

Some stupid muppet at qualitive team has not bothered to when you go into drop categories it doesnt remove the old ones as youd have thought any good database designer would.

 

1271 is actually reffering to an old entry in the:

 

xcart_categories_lng table:

 

ID      | Category_name

'1271'      'Old name for category' (this is the value thats appearing in the customer front end).

 

What I want it to do, is preferably before importing new categories.

 

Find values in the table xcart_categories_lng table that dont exist in xcart_categories table.

 

I hope this makes sense, as I just cant seem to work out an SQL query to perform this operation.

 

Thanks ever so much in advance for any replies,

Jeremy.

[j.smith1981]

This is a query I have tried performing:

 

DELETE

FROM 'xcart_categories_lng'

LEFT JOIN 'xcart_categories' ON 'xcart_categories_lng.categoryid' = 'xcart_categories.categoryid'

WHERE 'xcart_categories.categoryid' IS NULL

 

But it wont work, any ideas?

[fenway]

Why are you single quoting everything????

[j.smith1981]

Why are you single quoting everything????

 

Sorry its just as a pure example, its working what I have done now.

 

I should have updated this thread when I did that so apologies.

 

Just took a copy of the database as it was when I started and used that.

 

Took a primary key from the data that doesnt exist on another table.

 

Then used that and joined on the table that's meant to have the relevant information for the entities in question and used WHERE A2.attribute IS NULL

 

It now works.