Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] Table Design - SQL Query Question

Expand Messages
  • Frank Allen
    Another Solution, but not sure about the performance (1) Table :: Machine ... Id, Name, Model, related attributes 1 ABC 123 ... 2 XYZ
    Message 1 of 6 , Aug 11 1:58 AM
    • 0 Attachment
      Another Solution, but not sure about the performance

      (1)
      Table :: Machine
      -----------------------------------------------------
      Id, Name, Model, related attributes
      1 ABC 123 ...
      2 XYZ 789 ...
      .
      ..


      (2)
      Table :: MachineParts
      ------------------------------------------------------
      MachineId, Attribute, Value
      1 Color Black
      1 Color Green
      1 HD 80GB
      1 RAM 1GB
      2 Color White
      2 HD 160GB
      2 RAM 2.5GB

      Declare
      @Attribute varchar(256)

      --Set @Attribute = 'RAM'
      --Set @Attribute = 'HD'
      --Set @Attribute = NULL
      Set @Attribute = 'Color'


      Select MP.MachineId, M.Name, M.Model,...,MP.Attribute, MP.Value
      From Machine M
      Inner Join MachineParts MP On M.Id = MP.MachineId
      Where MP.Attribute = IsNull(@Attribute,MP.Attribute)
      ---< Use Of @Variable to get a multi purpose query)


      John Warner <john@...> wrote:
      Arnie, on the color question itself, as a design consideration I wonder
      of a look up table of color values might not be better. 1 = black, 2 =
      white, etc rather then having the 'redundant' black brown white etc ion
      the main table? Queries could then include the color word from the field
      if needed and the actual colors would be consistent:

      SELECT COUNT(colorField) as ColorCount
      FROM theTable
      WHERE colorField = "Color 1";

      John Warner

      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
      > Sent: Wednesday, August 09, 2006 7:59 PM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: RE: [SQLQueriesNoCode] Table Design - SQL Query Question
      >
      >
      > Which server product/version are you using?
      >
      > - Arnie Rowland
      >
      > "I am a great believer in luck, and I find that the harder I
      > work, the more I have of it." - Thomas Jefferson (1743-1826)
      >
      >
      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Modha Kumar
      > Sent: Wednesday, August 09, 2006 4:15 PM
      > To: Arnie
      > Subject: [SQLQueriesNoCode] Table Design - SQL Query Question
      >
      > Hi All
      >
      > I have to store a machine name and some config params (Name - Value
      > pairs).
      >
      > Ex:
      > Machine1 CPU - 1GB, Color - Black, Make-Dell
      > Machine2 CPU - 2GB Color- Brown Make -HP
      > etc
      > I have to store Color as well as Black because I am not sure how many
      > such name value pairs I have to store.
      >
      > Now I need to query such that , give me all machines for which color
      > is black. It should return me Machine1,...
      >
      > I am just looking for an idea how to do it.
      >
      > Please let me know.
      >
      > Thanks
      > bib





      __________________________________________________
      Do You Yahoo!?
      Tired of spam? Yahoo! Mail has the best spam protection around
      http://mail.yahoo.com

      [Non-text portions of this message have been removed]
    • John Warner
      Your example points out an interesting problem with your design, the impossible has been allowed to happen, you have a machine (1) that is two colors at
      Message 2 of 6 , Aug 11 11:19 AM
      • 0 Attachment
        Your example points out an interesting problem with your design, the
        'impossible' has been allowed to happen, you have a machine (1) that is
        two colors at once.

        John Warner


        > -----Original Message-----
        > From: SQLQueriesNoCode@yahoogroups.com
        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Frank Allen
        > Sent: Friday, August 11, 2006 4:58 AM
        > To: SQLQueriesNoCode@yahoogroups.com
        > Subject: RE: [SQLQueriesNoCode] Table Design - SQL Query Question
        >
        >
        > Another Solution, but not sure about the performance
        >
        > (1)
        > Table :: Machine
        > -----------------------------------------------------
        > Id, Name, Model, related attributes
        > 1 ABC 123 ...
        > 2 XYZ 789 ...
        > .
        > ..
        >
        >
        > (2)
        > Table :: MachineParts
        > ------------------------------------------------------
        > MachineId, Attribute, Value
        > 1 Color Black
        > 1 Color Green
        > 1 HD 80GB
        > 1 RAM 1GB
        > 2 Color White
        > 2 HD 160GB
        > 2 RAM 2.5GB
        >
        > Declare
        > @Attribute varchar(256)
        >
        > --Set @Attribute = 'RAM'
        > --Set @Attribute = 'HD'
        > --Set @Attribute = NULL
        > Set @Attribute = 'Color'
        >
        >
        > Select MP.MachineId, M.Name, M.Model,...,MP.Attribute, MP.Value
        > From Machine M
        > Inner Join MachineParts MP On M.Id = MP.MachineId
        > Where MP.Attribute = IsNull(@Attribute,MP.Attribute)
        > ---< Use Of @Variable to get a multi purpose query)
        >
        >
        > John Warner <john@...> wrote:
        > Arnie, on the color question itself, as a design
        > consideration I wonder of a look up table of color values
        > might not be better. 1 = black, 2 = white, etc rather then
        > having the 'redundant' black brown white etc ion the main
        > table? Queries could then include the color word from the
        > field if needed and the actual colors would be consistent:
        >
        > SELECT COUNT(colorField) as ColorCount
        > FROM theTable
        > WHERE colorField = "Color 1";
        >
        > John Warner
        >
        > > -----Original Message-----
        > > From: SQLQueriesNoCode@yahoogroups.com
        > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
        > > Sent: Wednesday, August 09, 2006 7:59 PM
        > > To: SQLQueriesNoCode@yahoogroups.com
        > > Subject: RE: [SQLQueriesNoCode] Table Design - SQL Query Question
        > >
        > >
        > > Which server product/version are you using?
        > >
        > > - Arnie Rowland
        > >
        > > "I am a great believer in luck, and I find that the harder I
        > > work, the more I have of it." - Thomas Jefferson (1743-1826)
        > >
        > >
        > > -----Original Message-----
        > > From: SQLQueriesNoCode@yahoogroups.com
        > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Modha Kumar
        > > Sent: Wednesday, August 09, 2006 4:15 PM
        > > To: Arnie
        > > Subject: [SQLQueriesNoCode] Table Design - SQL Query Question
        > >
        > > Hi All
        > >
        > > I have to store a machine name and some config params (Name - Value
        > > pairs).
        > >
        > > Ex:
        > > Machine1 CPU - 1GB, Color - Black, Make-Dell
        > > Machine2 CPU - 2GB Color- Brown Make -HP
        > > etc
        > > I have to store Color as well as Black because I am not
        > sure how many
        > > such name value pairs I have to store.
        > >
        > > Now I need to query such that , give me all machines for which color
        > > is black. It should return me Machine1,...
        > >
        > > I am just looking for an idea how to do it.
        > >
        > > Please let me know.
        > >
        > > Thanks
        > > bib
      Your message has been successfully submitted and would be delivered to recipients shortly.