> 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
> take more coding), I just added 5 Guest fields named Guest1-Guest5 (Yes,
> could argue about normalization here, but I'd rather not). When
> 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
> 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
> field, skip Guest2, and put something in Guest3. So, I need a better
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
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Database development questions? Check the forum!