Selecting conditionals - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Selecting conditionals

Author

Topic

initforthemoney
Starting Member

2 Posts

Posted - 2005-11-07 : 16:13:18

Hi

I am really stuck on this one. Just cant get my head around it. Maybe some one can help.

I have the following tables:

Table Name: Product
ID PK
Name

Table Name: Option
ID PK
Name

Table Name: OptionType
ID PK
OptionID FK
ProductName

Table Name: ProductOptionType
ID PK
ProductID FK
OptionID FK
OptionTypeID FK

Some example data for the ProductOptionType table looks like this:

ID ProductID OptionID OptionTypeID
1 1 1 1
2 1 2 3
3 2 1 1

I am trying to get all the products that have OptionTypeID = 1 and OptionTypeID = 3. In the example the result set would only bring back product ID 1 becuase OptionTypeID's 1 and 3 have been assigned. Product ID 2 will not come back because it only has OptionTypeID 1 assigned. I hope this is clear

Would you say this is a good DB design to add flexibility to a product? A product can be assigned many options like (Size, Colour, etc). An option can have many types (Colour can have Red, Green, Blue etc). An admin screen would assign each product with its specific options and option types which are stored in the ProductOptionType table.

The above query will be used in an advanced search form. The user would select from drop down lists (The OptionTypeID's) for example Colour: Red and Size: Large. Only products matching both critera are returned.

Maybe my db design could be improved to make the query easier?

Thanks

initforthemoney
Starting Member

2 Posts

Posted - 2005-11-07 : 19:50:40

Hi

As a newcomer to this forum I was just having a quick browse of some previous posts and luckily found this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55803

It solves my problem perfectly :-)

I tried this out and it worked:

select ProductID
from ProductOptionType
where OptionTypeID in (1, 4)
group by ProductID
having count(OptionTypeID) = 2

I could really do with a good book on SQL. My knowledge is not quite as good as I would like it to be. Any recommendations?

Cool forum

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-07 : 19:52:32

http://www.sqlteam.com/store.asp

I recommend Ken Henderson's books.

Tara Kizer

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-08 : 01:41:56

>>I could really do with a good book on SQL

Refer this also
http://vyaskn.tripod.com/sqlbooks.htm

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources