|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Select statement on multiple valuesI have a table as defined below that contains some configuraton values. ConfigurationID CharacteristicID CharacteristicValue --------------- ---------------- ------------------- 1234567890 Lenght 2300 1234567890 Height 1900 1234567890 Width 1800 1234567890 Color Red 0987654321 Lenght 3000 0987654321 Height 1900 0987654321 Width 1800 0987654321 Color Red I need to write an Sql statement to fnd the ConfiguationID having characteristic values, for example: I would like to know if there is a ConfigurationID that has the Lenght = 2300, Height = 1900, Width = 1800, Color = Red, returning the ConfigurationID. How could I write the select statement? Regards Antonio Google for "Relational Division" for the generic solutions. If you want
a complete and specific answer then it helps to include DDL and sample data INSERT statements with your post. -- David Portas SQL Server MVP -- why don't you use a table with columns
Lenght , Height, Width, Color? Is there any strong reason to complicate things? Aahh, the simple version of the good 'ol Entity-Attribute-Value design! A
popular topic in this newsgroup. Starting with the constructive critisism, you're requirement for your query illustrates one of the great flaws with this data model. While it does make adding new "characteristics" a breeze, obtaining useful information is made more difficult by this design (as you are seeing). Also, how do you ensure that, from a database level, developers/users cannot add a nonsensical row, such as 'Length = Red"? I am guessing that all you columns are varchar, so to implement decent data integrity, you would need to have a rather complicated set of check constraints on the table. And any time you need to use the numeric values for computation or comparison, you need to cast the values to the approriate data types. Unless your "Characteristics" change on a daily (or maybe even weekly) basis, the benefits of using this model are far outweighed by the problems. OK, enough grousing! Here is a solution that *may* give you what you are after. There are probably better ways to do this, but this one should get you by (untested, since you did not provide DDL http://www.aspfaq.com/etiquette.asp?id=5006 ): SELECT A.ConfigurationID FROM ConfigTable A INNER JOIN ConfigTable B ON A.ConfigurationID = B.ConfigurationID INNER JOIN ConfigTable C ON A.ConfigurationID = C.ConfigurationID INNER JOIN ConfigTable D ON A.ConfigurationID = D.ConfigurationID WHERE (A.CharacteristicID = 'Length' AND A.CharacteristicValue = '2300') AND (B.CharacteristicID = 'Height' AND A.CharacteristicValue = '1900') AND (A.CharacteristicID = 'Width' AND A.CharacteristicValue = '1800') AND (A.CharacteristicID = 'Color' AND A.CharacteristicValue = 'Red') Of course, if you had these characteristics set up as columns, it would be much simpler: SELECT ConfigurationID FROM ConfigTable WHERE Length = 2300 AND Height = 1900 AND Width = 1800 AND Color = Red IHTH Jeremy Show quote "Antonio Budano" <antonio.budano_REMOVE_THIS_@_REMOVE_THIS_poste.it> wrote in message news:umQCDlRxFHA.3556@TK2MSFTNGP12.phx.gbl... > Hi there, > > I have a table as defined below that contains some configuraton values. > > > ConfigurationID CharacteristicID CharacteristicValue > --------------- ---------------- ------------------- > 1234567890 Lenght 2300 > 1234567890 Height 1900 > 1234567890 Width 1800 > 1234567890 Color Red > 0987654321 Lenght 3000 > 0987654321 Height 1900 > 0987654321 Width 1800 > 0987654321 Color Red > > I need to write an Sql statement to fnd the ConfiguationID having > characteristic values, for example: > > I would like to know if there is a ConfigurationID that has the Lenght = > 2300, Height = 1900, Width = 1800, Color = Red, returning the > ConfigurationID. > > How could I write the select statement? > > Regards > Antonio > Ok,
I attach the DDL script file in order to clear the subject. The real concept is that in my application I need to create some configuration for materials that must be flexible. It is not known how many characteristics a material could have, as it depends of material type. So I created a ConfigurationElement table where I define characteristics and if it has fixed values (in this case the ConfigurationElementValue table is filled with allowed values) or it is a free feld. After that, the ConfigurationObject table contains the object and the ConfigurationObjectItem table contains all elements definable for that object. The ConfigurationObject value is then assigned to the material type. Now the problem start. I must choose between the flexibility to have illimitate possibility of having characterists (in this case the tables Configuration and ConfigurationItem will be used, but this complicate the select statement to check if a configuration already exists) and the option to simplify the select statement but limit the number of characteristics I can use (in this case the table Configuration_1 will be used, I must limit the number of characteristcs to 15, as SQL limits the number of fileds in a index to 16, one of them is the ConfigurationObject field). So, I am at the point to decide the way to go. Forgot to say that the ConfigurationID is a GUID that must be generated if the looking for configuration is not found. Thanks Antonio Show quote "Jeremy Williams" <jeremydw***@netscape.net> ha scritto nel messaggio [attached file: Configuration.sql]news:uWfEKOSxFHA.3300@TK2MSFTNGP09.phx.gbl... > Aahh, the simple version of the good 'ol Entity-Attribute-Value design! A > popular topic in this newsgroup. > > Starting with the constructive critisism, you're requirement for your > query > illustrates one of the great flaws with this data model. While it does > make > adding new "characteristics" a breeze, obtaining useful information is > made > more difficult by this design (as you are seeing). Also, how do you ensure > that, from a database level, developers/users cannot add a nonsensical > row, > such as 'Length = Red"? I am guessing that all you columns are varchar, so > to implement decent data integrity, you would need to have a rather > complicated set of check constraints on the table. And any time you need > to > use the numeric values for computation or comparison, you need to cast the > values to the approriate data types. Unless your "Characteristics" change > on > a daily (or maybe even weekly) basis, the benefits of using this model are > far outweighed by the problems. > > OK, enough grousing! Here is a solution that *may* give you what you are > after. There are probably better ways to do this, but this one should get > you by (untested, since you did not provide DDL > http://www.aspfaq.com/etiquette.asp?id=5006 ): > > SELECT > A.ConfigurationID > FROM > ConfigTable A INNER JOIN ConfigTable B > ON A.ConfigurationID = B.ConfigurationID > INNER JOIN ConfigTable C > ON A.ConfigurationID = C.ConfigurationID > INNER JOIN ConfigTable D > ON A.ConfigurationID = D.ConfigurationID > WHERE > (A.CharacteristicID = 'Length' AND A.CharacteristicValue = '2300') AND > (B.CharacteristicID = 'Height' AND A.CharacteristicValue = '1900') AND > (A.CharacteristicID = 'Width' AND A.CharacteristicValue = '1800') AND > (A.CharacteristicID = 'Color' AND A.CharacteristicValue = 'Red') > > Of course, if you had these characteristics set up as columns, it would be > much simpler: > > SELECT > ConfigurationID > FROM > ConfigTable > WHERE > Length = 2300 AND > Height = 1900 AND > Width = 1800 AND > Color = Red > > IHTH > Jeremy > > "Antonio Budano" <antonio.budano_REMOVE_THIS_@_REMOVE_THIS_poste.it> wrote > in message news:umQCDlRxFHA.3556@TK2MSFTNGP12.phx.gbl... >> Hi there, >> >> I have a table as defined below that contains some configuraton values. >> >> >> ConfigurationID CharacteristicID CharacteristicValue >> --------------- ---------------- ------------------- >> 1234567890 Lenght 2300 >> 1234567890 Height 1900 >> 1234567890 Width 1800 >> 1234567890 Color Red >> 0987654321 Lenght 3000 >> 0987654321 Height 1900 >> 0987654321 Width 1800 >> 0987654321 Color Red >> >> I need to write an Sql statement to fnd the ConfiguationID having >> characteristic values, for example: >> >> I would like to know if there is a ConfigurationID that has the Lenght = >> 2300, Height = 1900, Width = 1800, Color = Red, returning the >> ConfigurationID. >> >> How could I write the select statement? >> >> Regards >> Antonio >> > >  |
|||||||||||||||||||||||
Other interesting topics