That worked perfectly!!! Thanks so much :-)
-Original Message-
From: Diana Soares [mailto:[EMAIL PROTECTED]
Sent: Friday, December 05, 2003 10:31 AM
To: Robert Hughes
Cc: [EMAIL PROTECTED]
Subject: Re: Need help with query. Please!
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]
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.544 / Virus Database: 338 - Release Date: 11/25/2003
---
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
