List:General Discussion« Previous MessageNext Message »
From:Jesse Date:December 14 2005 7:03pm
Subject: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.

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 

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