Meaningless Drivel is fun!
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!