> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
