• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Problem

 
Amy Phillips
Ranch Hand
Posts: 280
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Rudy Dakota
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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,
    Rudy.
     
    David Peterson
    author
    Ranch Hand
    Posts: 154
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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:
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic