SQL Problem

[Amy Phillips]

Hi,
I have 2 tables, Attendee and Client_Company, and I need to display all client_companies with less than 2 female attendees. I can get the companies with 1 female to display but am stuck on how to also show those with no females.
Heres what I have so far

 select compname
from client_company, attendee
where attendee.company_id = client_company.company_ID (+)
and gender = 'F'
group by compname
having count(gender) < 2 

I have heard that I may need a case statement, which I'm guessing will only display those companies with less than or no females but I am totally lost on trying to get that one working.
Thanks very much

[Rudy Dakota]

Hi Amy,
This solution should work, though I suspect it is not the most elegant option:

select compname
from   client_company
      ,attendee
where  attendee.company_id = client_company.company_ID 
and    gender = 'F'
group  by compname
having count(gender) < 2 
union
select compname
from   client_company
where  company_ID not in (select company_id
                          from   attendee
                          where  gender = 'F')

Some remarks:

I have given this in my favorite format, which greatly improves readability :roll:

Done away with the outer join, 'cause I don't think it works due to the "gender = 'F'" clause

The other option would be to do something with the count clause, counting the females instead of filtering on them. There is a trap though: the companies without any attendees.

Hope this helps,
Rudy.

[David Peterson]

Alternatively you can use an OUTER JOIN so that the records in client_company show up even if there are no records in attendee.
The syntax for OUTER JOINs tends to vary between SQL dialects, but try the following:

SELECT compname
FROM client_company c
LEFT OUTER JOIN attendee a 
ON (
     a.company_id = c.company_id
     AND a.gender = "F"
   )
GROUP BY compname
HAVING COUNT(a.gender) < 2