List:General Discussion« Previous MessageNext Message »
From:SGreen Date:December 14 2005 7:22pm
Subject:Re: SQL Statement Help
View as plain text  
"Jesse" <jlc@stripped> 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


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