List:General Discussion« Previous MessageNext Message »
From:Charles Walmsley Date:December 14 2005 11:39pm
Subject:RE: SQL Statement Help
View as plain text  
Dear Jesse

Could I please reinforce what Shawn is implying and suggest you look again
at your table structure?  All my experience over many years with DB design
(admittedly not with MySql) suggest that you make have started to dig a
little hole but it could easily become a very large one.

With best wishes

Ch
-----Original Message-----
From: mysql-return-192841-chwalmsley=compuserve.com@stripped
[mailto:mysql-return-192841-chwalmsley=compuserve.com@stripped] On
Behalf Of SGreen@stripped
Sent: 14 December 2005 19:22
To: Jesse
Cc: mysql@stripped
Subject: Re: SQL Statement Help

 
"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