"Jesse" <[EMAIL PROTECTED]> wrote on 12/14/2005 02:03:22 PM:
> 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 > > Yes, I need to gently chide you about breaking normalization. You have traded a little bit of coding used to validate your data and populate the table with a whole bunch of coding to maintain and use it. The simple, robust query (from the normalized solution) should have looked something like this: SELECT p.name, count(g.id) FROM participant p LEFT JOIN guest g ON p.id = g.participant_id GROUP BY p.name; Instead you have to do conditional math across all 5 guest columns to get your data. If sometime in the future, someone decides that there should be up to 20 guests per participant, your table suddenly gets 4 times wider and this query (and every other query that uses this table, including your INSERT statements) becomes 4 times nastier. Doing it the other way would require no table changes, little to no query changes, and little to no reporting code modification if you decided to allow more than 5 guests per participant. Anyway, to get your guest counts, try this (assuming that any non-NULL value is a valid guest): SELECT name, 5-isnull(guest1)-isnull(guest2)-isnull(guest3)-isnull(guest4)-isnull(guest5) as guests FROM participant; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
