Re: Stuck on creating an easy view, please help

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Thanks (Thanks_at_discussions.microsoft.com)
Date: 09/09/04 

Date: Thu, 9 Sep 2004 06:43:05 -0700

Thanks for your answer. I had mention the problem with the table design but
redesign apparently is not an option -- that didn't make sense to me so now I
need to figure out a way to answer my question.

"Anith Sen" wrote:

> >> Or do you know of a better way to get the result I want?
>
> Yup. First, you have to reconsider your design. Since multiple values are
> concatenated to form a string for the team, your design suffers from severe
> logical problems inherent to multi-valued systems. A reasonable approach,
> based on your narrative would be to have the tables like:
>
> CREATE TABLE Requests (
> Request_id INT NOT NULL,
> Team_id INT NOT NULL
> REFERENCES Teams ( Team_id )
> PRIMARY KEY ( Request_id, Team )
> );
> CREATE TABLE Teams (
> Team_id INT NOT NULL PRIMARY KEY ,
> Descr VARCHAR(10) NOT NULL
> ) ;
>
> You can have your data like:
>
> INSERT Requests SELECT 240, 2 ;
> INSERT Requests SELECT 240, 3 ;
> INSERT Requests SELECT 241, 1 ;
> INSERT Requests SELECT 242, 1 ;
> INSERT Requests SELECT 242, 2 ;
> INSERT Requests SELECT 242, 3 ;
>
> INSERT Teams SELECT 1, 'SC' ;
> INSERT Teams SELECT 2, 'PE' ;
> INSERT Teams SELECT 3, 'CO' ;
>
> Most query expressions using SQL would be trivial with such a schema & what
> you are requesting is no different. However, if you are looking for a
> concatenated string as your output, I would recommend you extract the
> resultset & do the concatenation leveraging the client programming
> language's string manipulation functions.
>
> --
> Anith
>
>
>

Relevant Pages

  • dataset and more complex relational data
    ... I'm trying to design an addressbook, ... fieldvalue string); ... adr_id int, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: My fight with classes :)
    ... I got started to try a concatenation of 2 type of string, ... It was confusing to design your class in such a way that "k+'aks'" ...
    (comp.lang.python)
  • Generic IDictionary confusion
    ... trying to design an effiecient interface, ... int is some id and string^ is ...
    (microsoft.public.dotnet.languages.vc)
  • RE: How to group different data types for easy writing to a file?
    ... Type myType ... String1 as String ... Int as Integer ... > It's the concatenation step that I'm asking about - is it possible to ...
    (microsoft.public.vb.general.discussion)
  • Re: Converting char(s) into String
    ... I had a problem trying to concatenate a few characters into a String. ... Eclipse threw a "Type mismatch: cannot convert from int ... s1.charAt(0) returns a char, s1.charAtreturns a char. ... the '+' operator is only overloaded for String concatenation, not char concatenation to produce strings. ...
    (comp.lang.java.programmer)