SQL Help please

[Dave Shelley]

SQL guru's,

I could use some help writing a bit of SQL.
There's 3 tables:
orderable_parts
partID varchar,
topCatID int,  # top level category ID
...
part_attributes
partID varchar,
attName varchar,
attValue varchar,
...
topcatattributevalues
tcavID int,
topCatID int,
attName varchar,
attValue varchar,
...
orderable_parts has parts available on our web site. Theres about 40,000 of 
them
part_attributes are related to parts. Color, size etc. ~150,000 rows
topcatattributevalues is a list of all the distinct part attributes 
available in each top level category. They're used for web searches and for 
product managers to edit orderable_parts, ~100,000 rows

I'm trying to build a function to delete records from topcatattributevalues 
that are not used in any orderable_part. Either 1 delete statement or an 
update status=-1 and a delete where status=-1 would work. But it looks to me 
like I need a 3 way outer join and I can't seem to get it to work.

I tried a variety of statements that look something like:

update topcatattributevalues t
left outer join orderable_parts o on (t.topCatID=o.topCatID),
left outer join part_attributes p on (t.attName=p.attName and 
t.attValue=p.attValue and o.partID=p.partID)
set t.status=-1
where o.partID is null

I can't seem to get it.

Any assistance would be greatly appreciated.

Thanks.
Dave.
_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE*  
http://join.msn.com/?page=features/virus

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]