Is this good enough? SELECT prodid, count(1) matches FROM PRODUCT_SPECS WHERE (where SPEC_ID=1 and VALUE=YES) OR(and SPEC_ID=5 and VALUE>=28) GROUP BY prodid HAVING matches=2;
This query form is flexible enough so that if, for instance, you just wanted to rank products based on how well they match a set of criteria, you could leave out the HAVING clause and replace it with ORDER BY matches DESC. If you only cared about matching a 3 of 4 query terms, your WHERE clause would list all 4 terms but your HAVING clause would only check for 3 of them. Normally, these results would be cached into a temporary table and re-used in other places (for speed) but it is possible to join in several other tables if you need them and still get decent results from a single statement. IMHO, Not only is this database design a flexible storage system but the ability to easily poll for partial matches and easily determine gross matching rankings makes it useful for many applications. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER <[EMAIL PROTECTED]> wrote on 12/19/2005 08:28:36 AM: > Great, :) > But do you know how to write a good select query using this design? > For example if i want to select all TV with widescreen and inch > greater than 28? > > select * from PRODUCT_SPECS (where SPEC_ID=1 and VALUE=YES) > (and SPEC_ID=5 and VALUE>=28) > > this doesnt feel right... > > > > From: SGreen at unimin dot com > Date: December 10 2005 3:29am > Subject: Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??= > > --=_alternative 000D3711852570D3_= > Content-Type: text/plain; charset="US-ASCII" > > MUCH BETTER!! Sorry I doubted you. However you have to remember that > unless you declare a second numeric column in your PRODUCT_SPECS table > then everything will be treated as strings. Sorting will be as strings, > comparisons will be as strings, and any attempt to use them as numbers > while they are strings will invalidate any indexes. > > I would suggest a second DECIMAL column on your PRODUCT_SPECS table or be > prepared for performance hits whenever you need numeric ordering. If you > compare them alphabetically, "8" comes after "1" so "8" is greater than > "10", "100", "1000", "20", "30", or any other "word" that starts with a > "letter" smaller than "8". > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > Test USER <[EMAIL PROTECTED]> wrote on 12/09/2005 05:47:41 PM: > > > Oh, is it really such a bad design? Here is some more. > > What is substring matches, and why do i need them? > > > > > > TBL_PRODUCTS > > ID PRODUCTNAME > > 1 SAMSUNG TV > > 2 PHILIPS DVD-PLAYER > > 3 PHILIPS TV > > 4 MAXTOR DMAX > > 5 LaCie HARDDIVE > > > > --------------------------------------------- > > > > TBL_SPECS > > ID DETAIL > > 1 Widescreen > > 2 VCD > > 3 DiVX > > 4 Capacity > > 5 Inch > > > > > > --------------------------------------------- > > > > PRODUCT_SPECS > > PRODID SPECID VALUE > > 1 1 YES > > 1 5 32 > > 2 2 NO > > 2 3 3.11 > > 3 1 NO > > 3 5 28 > > 4 4 80 > > 5 4 120 > > > > ----------------------------------------- > > > > Thanks again for your help! > > > > Quoting [EMAIL PROTECTED]: > > > > > This sounds like a simple case of bad design. > > > > > > You need to be able to locate specific values for various product > > > descriptions but they are all mangled together into just one field. > You > > > end up trying to do substring matches and all hell breaks loose and > > > performance hits the skids. > > > > > > My suggestion is to somehow re-process your "value" column into > separate > > > > > > specific columns or child tables, one for each distinct value held in > > > the > > > "value" field. I can identify the potential values of `hdd_size`, > > > `monitor_size`, `monitor_resolution`,`hw_port`,`cd_supp_format`.... > > > > > > Your data is unmanageable in its present format and you need to scrub > > > and > > > massage it into shape before what you have will be marginally useful. > > > > > > Shawn Green > > > Database Administrator > > > Unimin Corporation - Spruce Pine > > > > > > > > > Test USER <[EMAIL PROTECTED]> wrote on 12/09/2005 04:56:21 PM: > > > > > > > Hi again :) > > > > > > > > The table contains a column named value and is in the format > > > varchar(255). > > > > This column contains specifications for different computer products. > > > > There is also a id column so i know which product it belongs to. > > > > > > > > value(varchar) > > > > 80 > > > > 17" > > > > 1024x768 > > > > USB > > > > DiVX > > > > > > > > For example, the first value 80 tells me with som joins that the > > > > product maxtor > > > > diamondmax has 80Gb capacity. And that a Philips DVD-player > > > supportsDiVX > > > for > > > > the last value in this example. > > > > > > > > Now i want to select all harddrvies with a capacity greater or equal > > > to > > > 80. > > > > Doing a "select value from tbl where value >=80 order by value > > > DESC"will > > > give > > > > som unexpected results. > > > > > > > > If you have 80, 120, 250 in the database the result will be: > > > > 80 > > > > 250 > > > > 120 > > > > > > > > I don't really know how to solve this other than to use CAST(value > as > > > SIGNED). > > > > Maybe i could rebuild the database but i don't know how a good > > > databasedesign > > > > for this would look like :) > > > > > > > > Thanks for you help! > > > > > > > > Quoting [EMAIL PROTECTED]: > > > > > > > > > I misunderstood, I thought you were looking for a way of > converting > > > your > > > > > > > > > > numbers-as-strings into a native numeric format. > > > > > > > > > > Please describe you situation better: What language are you using > to > > > > > build > > > > > your application. Are you composing the SQL statement client-side > or > > > > > > > > server-side? What kind of SQL statement are you trying to execute? > > > > > > > > > > Your table structures (the output of SHOW CREATE TABLE ... works > > > very > > > > > > > > well) and some sample data would also help. > > > > > > > > > > Sorry for the confusion! > > > > > > > > > > Shawn Green > > > > > Database Administrator > > > > > Unimin Corporation - Spruce Pine > > > > > > > > > > > > > > > Test USER <[EMAIL PROTECTED]> wrote on 12/09/2005 04:28:44 PM: > > > > > > > > > > > Hello, thanks for your help! > > > > > > I dont really get it :) > > > > > > > > > > > > You suggestion is to have a seperate column with the name > > > numericvalue > > > > > > > > > > and > > > > > > insert userinput into that and add a zero, right? > > > > > > > > > > > > Could you explain more, why when how will this help me :) > > > > > > > > > > > > Quoting [EMAIL PROTECTED]: > > > > > > > > > > > > > Assuming that your text data is in the column `userinput` and > > > you > > > > > want > > > > > > > the > > > > > > > integer values to be in the column `numericvalue`, this > > > statement > > > > > will > > > > > > > > > > > > populate the `numericvalue` column all at once: > > > > > > > > > > > > > > UPDATE `odd_data_table` SET `numericvalue` = `userinput` + 0; > > > > > > > > > > > > > > You are better off checking for type-correctness before you > > > enter > > > > > data > > > > > > > > > > > > into the database than you are trying to correct it after the > > > input. > > > > > > > > > > > > However, I have had to do just this kind of conversion on many > > > > > > occasions > > > > > > > > > > > > > > (old data, bad batch inputs, text file bulk loads, etc.) so I > > > > know > > > > > > > techniques like this still have their place. > > > > > > > > > > > > > > Shawn Green > > > > > > > Database Administrator > > > > > > > Unimin Corporation - Spruce Pine > > > > > > > > > > > > > > Test USER <[EMAIL PROTECTED]> wrote on 12/09/2005 03:30:17 PM: > > > > > > > > > > > > > > > in an application i have written there is the need to do a > > > search > > > > > from > > > > > > > > > > > > > > mysql > > > > > > > > using numbers that are stored in a varchar column. it is not > > > > > > > > > possible to store > > > > > > > > only the results with numbers in a seperate column. > > > > > > > > so i was looking at CAST(), is this a big performance loss? > is > > > > > > > theresome > > > > > > > way > > > > > > > > of benchmarking different queries easy? > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ------------------------------------------------- > > > > FREE E-MAIL IN 1 MINUTE! > > > > - [EMAIL PROTECTED] - http://www.pc.nu > > > > > > > > > > > > > > > ------------------------------------------------- > > FREE E-MAIL IN 1 MINUTE! > > - [EMAIL PROTECTED] - http://www.pc.nu > > --=_alternative 000D3711852570D3_=-- > > > > ------------------------------------------------- > FREE E-MAIL IN 1 MINUTE! > - [EMAIL PROTECTED] - http://www.pc.nu
