inversing an inner join without using left join

[mark_me]

Hi, I am developing an application. i have 3 main tables in mysql database . tables are table1 : products (1000 rows) table2 : parts table3 : products_parts (M:N relationship) contains over 40,000 rows with parts that can work with upto 1000 products. I need to select all products that have no specific part assigned. I tried a left join "select cols from products left join parts on products.id = products_parts.productid where partid!=3 and products_parts.productid is null" The statement is correct to my knowledge but because of too much data in relationship table, fatal timeout exception is thrown. i also tried using not in and not exists... Again statements are correct but i get fatal exception. I say statements are correct because they work with small tables... How will i get around this issue. For left join i would bring into consideration that product1 can work with part1,2,3 . At same time product2 can work with part 1,2 so with left join even if i say select products that doesn't work with part1 , i will get product 1 because association is with part3 this time... ( i don't know whether this was useful or not) Actually , i want an exact inverse of inner join (not using left join coz too much data throws fatal exception, not in and not exists also not working...) Is there any special command? Please provide help in this regard (i am using Asp.net with mysql) Thanks

[Ashfield]

If you are sure your sql is correct (and it does look OK to me) and your problem is actually the query timing out, how about either increase the query timeout (not sure how on MYSQL, but its probably part of the connection properties) or, better still, have a look at the indices. The table sizes your quote are very small, I would expect any database system to handle this volume of data with ease, but not if you are missing indices for your joins and there are tablescans going on.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[mark_me]

Thanks for replying. I used nested select statements and it worked. Left join wasn't returning proper rows because it was a many to many relationship table so product existed a number of times.