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.
I am using ASP.Net to display the data, and I'm using a DataGrid. They tend
to be a bit more difficult to figure out how to do this sort of thing with,
or I would probably have just coded it. If I can find a SQL Solution, that
would be best here.
Anyone have any ideas?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
