Try: SELECT C.company_id, C.company_name FROM companies C LEFT JOIN company_group_intersect CG ON (C.company_id=CG.company_id AND CG.group_id='1') WHERE C.status='1' AND CG.company_id IS NULL
-- Diana Soares On Fri, 2003-12-05 at 15:08, Robert Hughes wrote: > I have the following 3 tables: > > table 1: companies > fields 1: company_id, company name, status > Sample data: > 1 , company 1 , 0 > 2 , company 2 , 1 > 3 , company 3 , 1 > 4 , company 4 , 0 > 5 , company 5 , 1 > > table 2: groups > fields 2: group_id, group_name > Sample data: > 1 , Group 1 > 2 , Group 2 > 3 , Group 3 > 4 , Group 4 > 5 , Group 5 > > table 3: company_group_intersect > table 3: group_id, company_id > Sample data: > 1 , 2 > 1 , 3 > 2 , 2 > 2 , 3 > 2 , 5 > > As you can see, Group 1 consists of Companies 2 and 3. And Group 2 > consists of Companies 2, 3 and 5. > > The query I'm having trouble with is trying to get a result set of > (status=1) companies that AREN'T in a particular group. > > "group_id" is my only available variable passed in from the script. > > I need a results set that has: * companies.company_name, > companies.company_id where companies.status='1' and companies.company_id > is not in intersect table next to the variable group_id. > > If I pass in group_id 1 the result set should be: > 5 , company 5 > > since it's the only status='1' company that's not in group 1 > > Thanks in advance for your help. > > Robert > > --- > At Executive Performance Group we take security very seriously. All > emails and attachments are scanned for viruses prior to sending. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.544 / Virus Database: 338 - Release Date: 11/25/2003 > -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
