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
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
Joined: Jul 27, 2002
Hi Amy, This solution should work, though I suspect it is not the most elegant option:
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.
Joined: Oct 14, 2001
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: