List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:December 14 2005 7:11pm
Subject:Re: SQL Statement Help
View as plain text  

> Hello,
> I need help building a SQL Statement.  I'm currently using MySQL
> 5.something.  This one is kind of strange, and I can't seem to figure out
> how to do it.
>
> I have a table named Participants. Each Participant is allowed to bring up
> to 5 Guests.  Instead of putting the names in a separate table (which
would
> take more coding), I just added 5 Guest fields named Guest1-Guest5 (Yes,
we
> could argue about normalization here, but I'd rather not).  When
displaying
> this information in a grid, however, I would like to display the
> Participants name, and the number of guests they're bringing.  Example:
>
> Joe Blow        5
> Nancy Vila        2
> Henry Morgan        1
>
> How do I build a SQL Statement that will somehow give a count if a Guest
> field has something in it?  I could build a case statement something like
> this:
> CASE WHEN Guest1 IS NOT NULL THEN 1 WHEN Guest2 IS NOT NULL THEN 2 ... END
>
> However, I know how users are. Someone is gong to put a value in the
Guest1
> field, skip Guest2, and put something in Guest3.  So, I need a better
> solution.

LOL ... guess why normalization is your friend ... a simple "count" would
do the trick.

> Anyone have any ideas?

Can you use a Stored Procedure or Function to do your counting? That would
help, right?


Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Thread
SQL Statement HelpJesse14 Dec
  • Re: SQL Statement HelpMartijn Tonies14 Dec
  • Re: SQL Statement HelpSGreen14 Dec
    • RE: SQL Statement HelpCharles Walmsley15 Dec