File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes SQL Problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQL Problem" Watch "SQL Problem" New topic

SQL Problem

Amy Phillips
Ranch Hand

Joined: Apr 02, 2003
Posts: 280
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
Rudy Dakota
Ranch Hand

Joined: Jul 27, 2002
Posts: 54
Hi Amy,
This solution should work, though I suspect it is not the most elegant option:

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,
    David Peterson
    Ranch Hand

    Joined: Oct 14, 2001
    Posts: 154
    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:
    I agree. Here's the link:
    subject: SQL Problem
    It's not a secret anymore!