Many-to-Many Query Help

socca · #1 (**permalink**) 02-06-09, 16:08

I have a classical many-to-many DB design: Contractors_table, Trades_table, Contract_Trades.

I am looking to run a query that will return what trades this contractor does not do.

Any help will be greatly appreciated.

r937 · #2 (**permalink**) 02-06-09, 18:29

"trades this contractor does not do" --

Code:

SELECT t.id
     , t.name
  FROM Trades_table AS t
LEFT OUTER
  JOIN Contract_Trades AS ct
    ON ct.trade_id = t.id
   AND ct.contractor_id = 937
 WHERE ct.contractor_id IS NULL

bklr · #3 (**permalink**) 02-07-09, 03:33

hi socca,
if u have small data then try with not in r not exists also queries
according to performance exists will have more performance than in
i think so............

socca · #4 (**permalink**) 02-09-09, 08:43

Thank you r937 that worked perfectly.

Internet Services

Web Development

Digital Marketing

Consumer Tech