Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-09-05 : 09:44:10
|
| Brent writes "Ok..first...I REALLY did look over the site for something similar but didn't find it....here's the situation:I have three tables: 1) A table holding Products, 2) A table holding a "pool" of data fields that can be assigned to those products, and 3) a lookup table that holds the associated fields for the products (i.e. key field to product id, key field to field id and data.) Data fields are assigned to the product according to its classification, i.e. Radio, TV, Guitar, Car, whatever. For example, a Radio might have color, type and power supply fields associated with it, and a Guitar might have color, make and strings fields associated with it. In essence the data looks something like:RADIO (products table) (fields table) - (color) Green - (type) AM/FM - (power supply) AC/DCWhat I would like to do is something similar:(THIS DOESN'T WORK)Select name from products inner join fields on fields.productID = products.ID where fields.data = 'GREEN' AND fields.data = 'AM/FM'(THIS WORKS, BUT DOES NOT RETURN AN ACCURATE RESULT SET)Select name from products inner join fields on fields.productID = products.ID where fields.data = 'GREEN' OR fields.data = 'AM/FM'I know WHY the AND doesn't work and the OR does, but I don't know of a way to get the functionality I need....I need some way to be able to search the multiple data fields associated with a product using 'AND' statements.Is there hope or is this just bad database design? The goal was to be able to have a single table with products and store the associated data fields according to the type of product. It has worked well so far except for this search. Any help is appreciated!Thanks! Brent" |
|
|
|
|
|
|
|
Creating And'ed Queries From a Lookup Table