> 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
